DELETE

Purpose

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

• Prerequisites

• Syntax

• Keywords and Parameters

• Examples

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

Syntax

delete::=

Text description of delete

dml_table_expression_clause::=

Text description of dml_table_expression_clause

subquery: See SELECT.

subquery_restriction_clause::=

Text description of subquery_restriction_clause

table_collection_expression::=

Text description of table_collection_expression

where_clause::=

Text description of where_clause

returning_clause::=

Text description of returning_clause

Keywords and Parameters

hint

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

See Also: "Hints" and Oracle9i Database Performance Guide and Reference for the syntax and description of hints

from_clause

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.

dml_table_expression_clause

schema

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.

dblink

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

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

subquery_restriction_clause

Use the subquery_restriction_clause to restrict the subquery in one of the following ways:

• WITH READ ONLY indicates that the subquery cannot be updated.

• WITH CHECK OPTION indicates that Oracle prohibits any changes to that table that would produce rows that are not included in the subquery.

  See Also: "WITH CHECK OPTION Example"

table_collection_expression

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 built-in 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.

Note: 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 to delete only those rows that also exist in another table.

collection_expression

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

Restrictions on the dml_table_expression_clause

• You cannot execute this statement if table (or the base table of 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 view's defining query contains one of the following constructs:
  • A set operator

  • A DISTINCT operator

  • An aggregate or analytic 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 Oracle9i Database 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

table_collection_expression

where_clause

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

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

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

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.

Note: This alias is required if the dml_table_expression_clause references any object type attributes or object type methods.

returning_clause

The returning clause retrieves the rows affected by a DML (INSERT, UPDATE, or DELETE) 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.

expr

Each item in the expr list must be a valid expression syntax. All forms are valid except scalar subquery expressions.

INTO

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

data_item

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.

Restrictions:

• You cannot specify the returning_clause for a multitable insert.

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

  See Also: PL/SQL User's Guide and Reference for information on using the BULK COLLECT clause to return multiple values to collection variables

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

Examples

Basic Examples

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

DELETE FROM product_descriptions;

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:

DELETE FROM (SELECT * FROM employees)
    WHERE job_id = 'PU_CLERK'
    AND commission_pct < .1;

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 TABLE(SELECT projs FROM dept d WHERE d.dno = 123) p
   WHERE p.pno IN (123, 456) OR p.budgets > 456.78;

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 != 0;

RETURNING Clause Example

The following example returns column salary from the deleted rows and stores the result in bind array :1. (The bind array must already have been declared.)

DELETE FROM employees
   WHERE job_id = 'PU_CLERK' AND commission_pct < 100
   RETURNING salary INTO :1;