Skip Headers

Oracle® Database SQL Reference
10g Release 1 (10.1)

Part Number B10759-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF



Use the DELETE statement to remove rows from:

Additional Topics


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

For you to delete rows from an updatable materialized view, the materialized view must be in your own schema or you must have the DELETE object privilege on the materialized view.

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

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

You must also have the SELECT object privilege on the object from which you want to delete if:


Description of delete.gif follows
Description of the illustration delete.gif

(DML_table_expression_clause::=, where_clause::=, returning_clause ::=)

DML_table_expression_clause ::=
Description of DML_table_expression_clause.gif follows
Description of the illustration DML_table_expression_clause.gif

(subquery::=, subquery_restriction_clause::=, table_collection_expression::=)

Description of subquery_restriction_clause.gif follows
Description of the illustration subquery_restriction_clause.gif

Description of table_collection_expression.gif follows
Description of the illustration table_collection_expression.gif

Description of where_clause.gif follows
Description of the illustration where_clause.gif

returning_clause ::=
Description of returning_clause.gif follows
Description of the illustration returning_clause.gif



Specify a comment that passes instructions to the optimizer on choosing an execution plan for the statement.

See Also:

"Hints " and Oracle Database Performance Tuning Guide for the syntax and description of hints


Use the FROM clause to specify the database objects from which you are deleting rows.

The ONLY syntax is relevant only for views. Use the ONLY clause if the view in the FROM clause belongs to a view hierarchy and you do not want to delete rows from any of its subviews.



Specify the schema containing the table or view. If you omit schema, then Oracle Database assumes the table or view is in your own schema.

table | view | materialized view | subquery

Specify the name of a table, view, materialized view, or the column or columns resulting from a subquery, from which the rows are to be deleted.

When you delete rows from an updatable view, Oracle Database deletes rows from the base table.

You cannot delete rows from a read-only materialized view. If you delete rows from a writable materialized view, then the database removes the rows from the underlying container table. However, the deletions are overwritten at the next refresh operation.If you delete rows from an updatable materialized view that is part of a materialized view group, then the database also removes the corresponding rows from the master table.

If table or the base table of view or the master table of materialized_view contains one or more domain index columns, then this statements executes the appropriate indextype delete routine.

See Also:

Oracle 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.


Specify the name of the partition or subpartition targeted for deletes within the object.

You need not specify the partition name when deleting values from a partitioned object. However, in some cases, specifying the partition name is more efficient than a complicated where_clause.


Specify the complete or partial name of a database link to a remote database where the object is located. You can delete rows from a remote object only if you are using Oracle Database distributed functionality.

See Also:

"Referring to Objects in Remote Databases " for information on referring to database links and "Deleting Rows from a Remote Database: Example"

If you omit dblink, then the database assumes that the object is located on the local database.


The subquery_restriction_clause lets you restrict the subquery in one of the following ways:


Specify WITH READ ONLY to indicate that the table or view cannot be updated.


Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause.

CONSTRAINT constraint

Specify the name of the CHECK OPTION constraint. If you omit this identifier, then Oracle automatically assigns the constraint a name of the form SYS_Cn, where n is an integer that makes the constraint name unique within the database.


The table_collection_expression lets you inform Oracle that the value of collection_expression should be treated as a table for purposes of query and DML operations. The collection_expression can be a subquery, a column, a function, or a collection constructor. Regardless of its form, it must return a collection value—that is, a value whose type is nested table or varray. This process of extracting the elements of a collection is called collection unnesting.


In earlier releases of Oracle, when collection_expression was a subquery, table_collection_expression was expressed as THE subquery. That usage is now deprecated.

You can use a table_collection_expression in a correlated subquery to delete rows with values that also exist in another table.


Specify a subquery that selects a nested table column from the object from which you are deleting.

Restrictions on the DML_table_expression Clause
  • You cannot execute this statement if table or the base or master table of view or materialized_view contains any domain indexes marked IN_PROGRESS or FAILED.

  • You cannot insert into a partition if any affected index partitions are marked UNUSABLE.

  • You cannot specify the ORDER BY clause in the subquery of the DML_table_expression_clause.

  • You cannot delete from a view except through INSTEAD OF triggers if the defining query of the view contains one of the following constructs:

A set operator
a DISTINCT operator
An aggregate or analytic function
A collection expression in a SELECT list
A subquery in a SELECT list
A subquery designated WITH READ ONLY
Joins, with some exceptions, as documented in Oracle Database Administrator's Guide

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 initialization parameter has been set to true.

See Also:



Use the where_clause to delete only rows that satisfy the condition. The condition can reference the object from which you are deleting and can contain a subquery. You can delete rows from a remote object only if you are using Oracle Database distributed functionality. Please refer to Chapter 6, " Conditions" for the syntax of condition.

If this clause contains a subquery that refers to remote objects, then the DELETE operation can run in parallel as long as the reference does not loop back to an object on the local database. However, if the subquery in the DML_table_expression_clause refers to any remote objects, then the DELETE operation will run serially without notification. Please refer to the parallel_clause in the CREATE TABLE documentation for additional information.

If you omit dblink, then the database assumes that the table or view is located on the local database.

If you omit the where_clause, then the database deletes all rows of the object.


Provide a correlation name for the table, view, materialized view, subquery, or collection value to be referenced elsewhere in the statement. This alias is required if the DML_table_expression_clause references any object type attributes or object type methods. Table aliases are generally used in DELETE statements with correlated queries.


This clause lets you return values from deleted columns, and thereby eliminate the need to issue a SELECT statement following the DELETE statement.

The returning clause retrieves the rows affected by a DML statement. You can specify this clause for tables and materialized views and for views with a single base table.

When operating on a single row, a DML statement with a returning_clause can retrieve column expressions using the affected row, rowid, and REFs to the affected row and store them in host variables or PL/SQL variables.

When operating on multiple rows, a DML statement with the returning_clause stores values from expressions, rowids, and REFs involving the affected rows in bind arrays.


Each item in the expr list must be a valid expression syntax.


The INTO clause indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item list.


Each data_item is a host variable or PL/SQL variable that stores the retrieved expr value.

For each expression in the RETURNING list, you must specify a corresponding type-compatible PL/SQL variable or host variable in the INTO list.


The following restrictions apply to the RETURNING clause:

See Also:


Deleting Rows: Examples

The following statement deletes all rows from the sample table oe.product_descriptions where the value of the language_id column is AR:

DELETE FROM product_descriptions
   WHERE language_id = 'AR';

The following statement deletes from the sample table hr.employees purchasing clerks whose commission rate is less than 10%:

DELETE FROM employees
   WHERE job_id = 'PU_CLERK'
   AND commission_pct < .1;

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

   WHERE job_id = 'PU_CLERK'
   AND commission_pct < .1;

Deleting Rows from a Remote Database: Example

The following statement deletes specified rows from the locations table owned by the user hr on a database accessible by the database link remote:

DELETE FROM hr.locations@remote
   WHERE location_id > 3000;

Deleting Nested Table Rows: Example

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

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

Deleting Rows from a Partition: Example

The following example removes rows from partition sales_q1_1998 of the sh.sales table:

DELETE FROM sales PARTITION (sales_q1_1998)
   WHERE amount_sold > 10000;

Using the RETURNING Clause: Example

The following example returns column salary from the deleted rows and stores the result in bind variable :bnd1. The bind variable must already have been declared.

DELETE FROM employees
   WHERE job_id = 'SA_REP' 
   AND hire_date + TO_YMINTERVAL('01-00') < SYSDATE;
   RETURNING salary INTO :bnd1;