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 [hint] [FIRST NumRows] FROM [Owner.]TableName [CorrelationName] 
[WHERE SearchCondition]
[RETURNING|RETURN Expression[,...]INTO DataItem[,...]]

Parameters

Parameter Description

hint

Specifies a statement level optimizer hint for the DELETE statement. See Statement Level Optimizer Hints for information on optimizer hints.

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 an alias for the immediately preceding table. Use the correlation name to reference the table elsewhere in the DELETE statement. The scope of the CorrelationName is the SQL statement in which it is used. It must conform to the syntax rules for a basic name. See Basic Names for details.

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 Expressions for details.

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.

  • If your table has out of line columns and there are millions of rows to delete, consider calling the ttCompact built-in procedure to free memory.

  • 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 FORALL and BULK COLLECT Operations in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for information about BULK COLLECT.

    • 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