DELETE

Syntax

DML_table_expression_clause::=

subquery: See "SELECT and Subqueries".

with_clause::=

table_collection_expression::=

where_clause::=

returning_clause::=

Purpose

To remove rows from a table, a partitioned table, a view's base table, or a view's partitioned base table.

Prerequisites

For you to delete rows from a table, the table must be in your own schema or you must have DELETE privilege on the table.

For you to delete rows from the base table of a view, the owner of the schema containing the view must have DELETE privilege on the base table. Also, if the view is in a schema other than your own, you must be granted DELETE privilege on the view.

The DELETE ANY TABLE system privilege also allows you to delete rows from any table or table partition, or any view's base table.

If the SQL92_SECURITY initialization parameter is set to TRUE, then you must have SELECT privilege on the table to perform a DELETE that references table columns (such as the columns in a where_clause).

Keywords and Parameters

hint	is a comment that passes instructions to the optimizer on choosing an execution plan for the statement. For the syntax and description of hints, see "Hints" and Oracle8i Designing and Tuning for Performance.
DML_table_expression_clause
schema	is the schema containing the table or view. If you omit schema, Oracle assumes the table or view is in your own schema.
table | view |snapshot | subquery	is the name of a table or view, or the column or columns resulting from a subquery, from which the rows are to be deleted. If you specify view, Oracle deletes rows from the view's base table. If table (or the base table of view) contains one or more domain index columns, this statements executes the appropriate indextype delete routine. See Also: Oracle8i Data Cartridge Developer's Guide for more information on these routines.
	Issuing a DELETE statement against a table fires any DELETE triggers defined on the table. All table or index space released by the deleted rows is retained by the table and index.
	Restrictions: You cannot execute this statement if table (or the base table of view) contains any domain indexes marked LOADING or FAILED. You cannot specify the ORDER BY clause in the subquery of the DML_query_expression_clause. You cannot delete from a view except through INSTEAD OF triggers if the view's defining query contains one of the following constructs: - A set operator - A DISTINCT operator - An aggregate function - A GROUP BY, ORDER BY, CONNECT BY, or START WITH clause - A collection expression in a SELECT list - A subquery in a SELECT list - Joins (with some exceptions). See Oracle8i Administrator's Guide for details. If you specify an index, index partition, or index subpartition that has been marked UNUSABLE, the DELETE statement will fail unless the SKIP_UNUSABLE_INDEXES parameter has been set to TRUE. See Also: "ALTER SESSION".
PARTITION (partition_name) | SUBPARTITION (subpartition_name)	specifies that partition_name or subpartition_name is the name of the partition or subpartition within table targeted for deletes. You need not specify the partition name when deleting values from a partitioned table. However, in some cases, specifying the partition name is more efficient than a complicated where_clause.
dblink	is the complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see "Referring to Objects in Remote Databases". You can delete rows from a remote table or view only if you are using Oracle's distributed functionality.
	If you omit dblink, Oracle assumes that the table or view is located on the local database.
with_clause	restricts the subquery in one of the following ways:
	WITH READ ONLY specifies that the subquery cannot be updated.
	WITH CHECK OPTION specifies that Oracle prohibits any changes to that table that would produce rows that are not included in the subquery. See "WITH CHECK OPTION Example".
table_collection_expression	informs Oracle that the collection value expression should be treated as a table. You can use a table_collection_expression to delete only those rows that also exist in another table.
	collection_expression	is a subquery that selects a nested table column from table or view.
	Note: In earlier releases of Oracle, table_collection_expression was expressed as "THE subquery". That usage is now deprecated.
where_clause	deletes only rows that satisfy the condition. The condition can reference the table and can contain a subquery. See the syntax description in "Conditions". You can delete rows from a remote table or view only if you are using Oracle's distributed functionality.
	Note: If this clause contains a subquery that refers to remote objects, the DELETE operation can run in parallel as long as the reference does not loop back to an object on the local database. (See the description of the parallel_clause in "CREATE TABLE".) However, if the subquery in the DML_query_expression_clause refers to any remote objects, the UPDATE operation will run serially without notification.
	If you omit dblink, Oracle assumes that the table or view is located on the local database. If you omit the where_clause, Oracle deletes all rows of the table or view.
t_alias	provides a correlation name for the table, view, subquery, or collection value to be referenced elsewhere in the statement. Table aliases are generally used in DELETE statements with correlated queries.
	Note: This alias is required if the DML_query_expression_clause references any object type attributes or object type methods.
returning_clause	retrieves the rows affected by the DELETE statement.
	You can use a returning_clause to return values from deleted columns, and thereby eliminate the need to issue a SELECT statement following the DELETE statement. When deleting a single row, a DELETE statement with a returning_clause can retrieve column expressions using the deleted row, rowid, and REFs to the deleted row and store them in PL/SQL variables or bind variables. When deleting multiple rows, a DELETE statement with the returning_clause stores values from expressions, rowids, and REFs involving the deleted rows in bind arrays. You can also use DELETE with a returning_clause to delete from views with single base tables. For host binds, the datatype and size of the expression must be compatible with the bind variable.
	expr	is any of the syntax descriptions in "Expressions". You must specify a column expression in the returning_clause for each variable in the data_item list.
	INTO	indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item list.
	data_item	is a PL/SQL variable or bind variable that stores the retrieved expr value.
	Restrictions: You cannot use this clause with parallel DML or with remote objects. You cannot retrieve LONG types with this clause. You cannot specify this clause for a view on which an INSTEAD OF trigger has been defined.

Examples

Basic Examples

The following statement deletes all rows from a table named TEMP_ASSIGN.

DELETE FROM temp_assign;

The following statement deletes from the EMP table all sales staff who made less than $100 commission last month:

DELETE FROM emp
    WHERE JOB = 'SALESMAN'
    AND COMM < 100;

The following statement has the same effect as the preceding example, but uses a subquery:

DELETE FROM (select * from emp)
    WHERE JOB = 'SALESMAN'
    AND COMM < 100;

Remote Database Example

The following statement deletes all rows from the ACCOUNTS table owned by the user BLAKE on a database accessible by the database link DALLAS:

DELETE FROM blake.accounts@dallas;

Nested Table Example

The following example deletes rows of nested table PROJS where the department number is either 123 or 456, or the department's budget is greater than 456.78:

DELETE THE(SELECT projs
           FROM dept d WHERE d.dno = 123) AS p
   WHERE p.pno IN (123, 456) OR p.budgets > 456.78;

Partition Example

The following example removes rows from partition NOV98 of the SALES table:

DELETE FROM sales PARTITION (nov98)
   WHERE amount_of_sale != 0;

Example

The following example returns column SAL from the deleted rows and stores the result in bind array :1:

DELETE FROM emp
   WHERE job = 'SALESMAN' AND COMM < 100
   RETURNING sal INTO :1;