Purpose

Additional Topics

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

The object is on a remote database or

The SQL92_SECURITY initialization parameter is set to TRUE and the DELETE operation references table columns (such as the columns in a 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.

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

subquery_restriction_clause

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

WITH READ ONLY

Specify WITHREADONLY to indicate that the table or view cannot be updated.

WITH CHECK OPTION

Specify WITHCHECKOPTION to indicate that Oracle prohibits any changes to the table or view that would produce rows that are not included in the subquery.

CONSTRAINT constraint

Specify the name of the CHECKOPTION constraint. If you omit this identifier, 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.

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 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 in a correlated subquery to delete rows with values that also exist in another table.

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.

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 CREATETABLE for additional information.

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 on the RETURNING Clause

You cannot:

Specify the returning_clause for a multitable insert.

Use this clause with parallel DML or with remote objects.

Retrieve LONG types with this clause.

Specify this clause for a view on which an INSTEADOF trigger has been defined.