Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
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.
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:
SQL92_SECURITY
initialization parameter is set to TRUE
and the DELETE
operation references table columns (such as the columns in a where_clause).delete::=
DML_table_expression_clause::=
subquery_restriction_clause::=
table_collection_expression::=
where_clause::=
Specify a comment that passes instructions to the optimizer on choosing an execution plan for the statement.
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.
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.
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.
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:
Specify WITH
READ
ONLY
to indicate that the table or view cannot be updated.
Specify WITH
CHECK
OPTION
to indicate that Oracle prohibits any changes to the table or view that would produce rows that are not included in the subquery.
Specify the name of the CHECK
OPTION
constraint. If you omit this identifier, Oracle automatically assigns the constraint a name of the form SYS_C
n
, 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.
Note: In earlier releases of Oracle, when |
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 table or view.
Note: In earlier releases of Oracle, table_collection_expression was expressed as " |
IN_PROGRESS
or FAILED.
UNUSABLE
.ORDER
BY
clause in the subquery of the dml_table_expression_clause.INSTEAD
OF
triggers if the view's defining query contains one of the following constructs:
DISTINCT
operatorGROUP
BY
, ORDER
BY
, CONNECT
BY
, or START
WITH
clauseSELECT
listSELECT
listIf 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
.
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 |
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.
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 |
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.
Each item in the expr
list must be a valid expression syntax. All forms are valid except scalar subquery expressions.
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.
You cannot:
returning_clause
for a multitable insert.LONG
types with this clause.INSTEAD
OF
trigger has been defined.
See Also:
PL/SQL User's Guide and Reference for information on using the |
Note: This clause lets you return values from deleted columns, and thereby eliminate the need to issue a |
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:
DELETE FROM (SELECT * FROM employees) WHERE job_id = 'PU_CLERK' AND commission_pct < .1;
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;
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;
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;
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;