DELETE

Name

DELETE -- delete rows of a table

Synopsis

DELETE [ <optimizer_hint> ] FROM <table>[@<dblink> ]
  [ WHERE <condition> ]
  [ RETURNING <return_expression> [, ...]
      { INTO { <record> | <variable> [, ...] }
      | BULK COLLECT INTO <collection> [, ...] } ]

Description

DELETE deletes rows that satisfy the WHERE clause from the specified table. If the WHERE clause is absent, the effect is to delete all rows in the table. The result is a valid, but empty table.

Note

The TRUNCATE command provides a faster mechanism to remove all rows from a table.

The RETURNING INTO { record | variable [, ...] } clause may only be specified if the DELETE command is used within an SPL program. In addition the result set of the DELETE command must not include more than one row, otherwise an exception is thrown. If the result set is empty, then the contents of the target record or variables are set to null.

The RETURNING BULK COLLECT INTO collection [, ...] clause may only be specified if the DELETE command is used within an SPL program. If more than one collection is specified as the target of the BULK COLLECT INTO clause, then each collection must consist of a single, scalar field – i.e., collection must not be a record. The result set of the DELETE command may contain none, one, or more rows. return_expression evaluated for each row of the result set, becomes an element in collection starting with the first element. Any existing rows in collection are deleted. If the result set is empty, then collection will be empty.

You must have the DELETE privilege on the table to delete from it, as well as the SELECT privilege for any table whose values are read in the condition.

Parameters

optimizer_hint

Comment-embedded hints to the optimizer for selection of an execution plan.

table

The name (optionally schema-qualified) of an existing table.

dblink

Database link name identifying a remote database. See the CREATE DATABASE LINK command for information on database links.

condition

A value expression that returns a value of type BOOLEAN that determines the rows which are to be deleted.

return_expression

An expression that may include one or more columns from table. If a column name from table is specified in the return_expression, the value substituted for the column when return_expression is evaluated is the value from the deleted row.

record

A record whose field the evaluated return_expression is to be assigned. The first return_expression is assigned to the first field in record, the second return_expression is assigned to the second field in record, etc. The number of fields in record must exactly match the number of expressions and the fields must be type-compatible with their assigned expressions.

variable

A variable to which the evaluated return_expression is to be assigned. If more than one return_expression and variable are specified, the first return_expression is assigned to the first variable, the second return_expression is assigned to the second variable, etc. The number of variables specified following the INTO keyword must exactly match the number of expressions following the RETURNING keyword and the variables must be type-compatible with their assigned expressions.

collection

A collection in which an element is created from the evaluated return_expression. There can be either a single collection which may be a collection of a single field or a collection of a record type, or there may be more than one collection in which case each collection must consist of a single field. The number of return expressions must match in number and order the number of fields in all specified collections. Each corresponding return_expression and collection field must be type-compatible.

Examples

Delete all rows for employee 7900 from the jobhist table:

DELETE FROM jobhist WHERE empno = 7900;

Clear the table jobhist:

DELETE FROM jobhist;

See Also

TRUNCATE