Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
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 a table, a partitioned table, a view's base table, or a view's partitioned base table.

Additional Topics


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.

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



Text description of statements_812.gif follows
Text description of delete

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


Text description of statements_839.gif follows
Text description of DML_table_expression_clause

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


Text description of statements_811.gif follows
Text description of subquery_restriction_clause


Text description of statements_840.gif follows
Text description of table_collection_expression


Text description of statements_841.gif follows
Text description of where_clause


Text description of statements_842.gif follows
Text description of returning_clause




See Also:

"Hints" and Oracle9i Database Performance Tuning Guide and Reference 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 only relevant 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, Oracle assumes the table or view is in your own schema.

table | view | materialized view | subquery

Specify 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:

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

PARTITION (partition_name) and SUBPARTITION (subpartition_name)

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

See Also:

"Deleting Rows from a Partition: Example"


Specify the complete or partial name of a database link to a remote database where the table or view is located. You can delete rows from a remote table or view only if you are using Oracle's 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, Oracle assumes that the table or view is located on the local database.


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





CONSTRAINT constraint


See Also:

"Using the WITH CHECK OPTION Clause: Example"





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

See Also:

"Table Collections: Examples"


Specify a subquery that selects a nested table column from table or view.


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

Restrictions on the dml_table_expression_clause

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:




Use the where_clause to delete only rows that satisfy the condition. The condition can reference the table and can contain a subquery. You can delete rows from a remote table or view only if you are using Oracle's distributed functionality.

See Also:

Chapter 5, "Conditions" for the syntax of condition


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. However, if the subquery in the dml_table_expression_clause refers to any remote objects, the UPDATE operation will run serially without notification. Please refer to the parallel_clause for CREATE TABLE for additional information.


If you omit the where_clause, Oracle deletes all rows of the table or view.


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














Restrictions on the RETURNING Clause

You cannot:


Deleting Rows: Examples

The following statement deletes all rows from the sample table oe.product_descriptions:

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's 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;