Deletes rows of a table.

Syntax

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

Description

You can use the DELETE command to delete rows that satisfy the WHERE clause from the specified table. If you do not specify the WHERE clause, all rows in the table are deleted. The result is valid, and the table becomes empty.

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

If you use the DELETE command within an SPL program, you can specify the RETURNING INTO { record | variable [, ...] } clause. In addition, the result set of the DELETE command must not include multiple rows. Otherwise, an exception occurs. If the result set is empty, the content of the target record or variables is set to null.

If you use the DELETE command within an SPL program, you can specify the RETURNING BULK COLLECT INTO collection [, ...] clause. If you specify multiple collection as the target of the BULK COLLECT INTO clause, each collection must consist of a single scalar field. collection must not be a record. The result set of the DELETE command may contain zero, one, or more rows. return_expression evaluated for each row of the result set becomes an element in collection, starting from the first element. Existing rows in collection are deleted. If the result set is empty, collection is empty.

You must have the DELETE privilege on the table to delete rows from it, and the SELECT privilege on tables whose data is read in the condition.

Parameters

Parameter Description
optimizer_hint Comment-embedded hints to the optimizer, which is used to select execution plan.
table The name of an existing table. The name can be schema-qualified.
dblink The database link name, which identifies a remote database. For more information about database links, see the CREATE DATABASE LINK command.
condition A value expression that returns a value of the BOOLEAN type. The value expression determines the rows to be deleted.
return_expression An expression that can include one or more columns in table. If a column name in table is specified in return_expression, the value substituted for the column when return_expression is evaluated is the value from the deleted row.
record A record to whose field you want to assign the evaluation result of return_expression. For example, the first return_expression is assigned to the first field in record, and the second return_expression is assigned to the second field in record. The number of fields in record must match the number of expressions, and the fields must be type-compatible with the corresponding expressions.
variable A variable to which you want to assign the evaluation result of return_expression. If you specify multiple return_expression and variable, the first return_expression is assigned to the first variable, the second return_expression is assigned to the second variable. The number of the specified variables that follow the INTO keyword must match the number of expressions that follow the RETURNING keyword, and the variables must be type-compatible with the corresponding expressions.
collection A collection in which an element is created from the evaluated return_expression. You can specify a collection of a single field or a collection of a record type. You can also specify multiple collections where each collection consists of a single field. The number of return expressions must match in number and order the number of fields in all specified collections. Each return_expression must be type-compatible with the corresponding collection field.

Examples

Delete all rows for employee 7900 from the jobhist table:

DELETE FROM jobhist WHERE empno = 7900;

Clear the jobhist table:

DELETE FROM jobhist;