Deletes rows of a table.
Syntax
DELETE [ optimizer_hint ] FROM table[subquery][@dblink ]
[ WHERE condition ]
[ RETURNING return_expression [, ...]
{ INTO { record | variable [, ...] }
| BULK COLLECT INTO collection [, ...] } ]Description
You can use a DELETE statement to delete rows that meet the WHERE conditions from the specified table. If you do not specify the WHERE clause, all rows in the table are deleted. The statement can be normally executed,
and the table becomes empty.
TRUNCATE is a faster method for deleting all rows from a table.
You can specify the RETURNING INTO { record | variable [, ...] clause only if you use the DELETE command within an SPL program. The result set of a 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.
You can specify the RETURNING BULK COLLECT INTO collection [, ...] clause only if you use the DELETE command within an SPL program. If you specify multiple collection as the target of the BULK COLLECT INTO clause, each collection must contain a scalar field. collection must not be a record. The result set of the DELETE command may contain zero, one, or multiple 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 from which you want to delete data, 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 an 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.
|
| subquery | This parameter specifies a subquery clause. |
Example
Delete all rows for employee 7900 from the jobhist table:
DELETE FROM jobhist WHERE empno = 7900;Clear thejobhist table:
DELETE FROM jobhist;Take the result of SELECT * FROM t as an object, and delete all the data of the object:
DELETE FROM (SELECT * FROM t);