DELETE

The DELETE statement deletes rows from a table.

Required privilege

No privilege is required for the table owner.

DELETE on the table for another user's table.

SQL syntax

DELETE [FIRST NumRows] FROM [Owner.]TableName[CorrelationName] 
[WHERE SearchCondition]
[RETURNING|RETURN Expression[,...]INTO DataItem[,...]]

Parameters

The DELETE statement has the parameters:

Parameter	Description
`FIRST` `NumRows`	Specifies the number of rows to delete. FIRST `NumRows` is not supported in subquery statements. `NumRows` must be either a positive INTEGER or a dynamic parameter placeholder. The syntax for a dynamic parameter placeholder is either `?` or :`DynamicParameter`. The value of the dynamic parameter is supplied when the statement is executed.
`[Owner.]TableName` [`CorrelationName]`	Designates a table from which any rows satisfying the search condition are to be deleted. `[Owner.]TableName` identifies a table to be deleted. `CorrelationName` specifies a synonym for the immediately preceding table. When accessing columns of that table, use the correlation name instead of the actual table name within the DELETE statement. The correlation name must conform to the syntax rules for a basic name. See "Basic names".
`SearchCondition`	Specifies which rows are to be deleted. If no rows satisfy the search condition, the table is not changed. If the WHERE clause is omitted, all rows are deleted. The search condition can contain a subquery.
`Expression`	Valid expression syntax. See Chapter 3, "Expressions".
`DataItem`	Host variable or PL/SQL variable that stores the retrieved `Expression` value.

Description

If all the rows of a table are deleted, the table is empty but continues to exist until you issue a DROP TABLE statement.
The DELETE operation fails if it violates any foreign key constraint. See "CREATE TABLE" for a description of the foreign key constraint.
The total number of rows reported by the DELETE statement does not include rows deleted from child tables as a result of the ON DELETE CASCADE action.
If ON DELETE CASCADE is specified on a foreign key constraint for a child table, a user can delete rows from a parent table for which the user has the DELETE privilege without requiring explicit DELETE privilege on the child table.
Restrictions on the RETURNING clause:
- Each Expression must be a simple expression. Aggregate functions are not supported.
- You cannot return a sequence number into an OUT parameter.
- ROWNUM and subqueries cannot be used in the RETURNING clause.
- Parameters in the RETURNING clause cannot be duplicated anywhere in the DELETE statement.
- Using the RETURNING clause to return multiple rows requires PL/SQL BULK COLLECT functionality. See Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.
- In PL/SQL, you cannot use a RETURNING clause with a WHERE CURRENT operation.

Examples

Rows for orders whose quantity is less than 50 are deleted.

DELETE FROM purchasing.orderitems
WHERE quantity < 50;

The following query deletes all the duplicate orders assuming that id is not a primary key:

DELETE FROM orders a
WHERE EXISTS (SELECT 1 FROM orders b
WHERE a.id = b.id and a.rowid < b.rowid);

The following sequence of statements causes a foreign key violation.

CREATE TABLE master (name CHAR(30), id CHAR(4) NOT NULL PRIMARY KEY);
CREATE TABLE details
  (masterid CHAR(4),description VARCHAR(200), 
   FOREIGN KEY (masterid) REFERENCES master(id));
INSERT INTO master('Elephant', '0001');
INSERT INTO details('0001', 'A VERY BIG ANIMAL');
DELETE FROM master WHERE id = '0001';

If you attempt to delete a "busy" table, an error results. In this example, t1 is a "busy" table that is a parent table with foreign key constraints based on it.

CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL,
       PRIMARY KEY (a));
CREATE TABLE t2 (c INT NOT NULL, 
       FOREIGN KEY (c) REFERENCES t1(a));
INSERT INTO t1 VALUES (1,1);
INSERT INTO t2 VALUES (1);
DELETE FROM t1;

An error is returned:

SQL ERROR (3001):  Foreign key violation [TTFOREIGN_0] a row in child table T2 has a parent in the delete range.

Delete an employee from employees. Declare empid and name as variables with the same data types as employee_id and last_name. Delete the row, returning employee_id and last_name into the variables. Verify that the correct row was deleted.

Command> VARIABLE empid NUMBER(6) NOT NULL;
Command> VARIABLE name VARCHAR2(25) INLINE NOT NULL;
Command> DELETE FROM employees WHERE last_name='Ernst'
       > RETURNING employee_id, last_name INTO :empid,:name;
1 row deleted.
Command> PRINT empid name;
EMPID                : 104
NAME                 : Ernst