Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 7 of 10
DML_table_expression_clause::=
subquery: See "SELECT and Subqueries".
table_collection_expression::=
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.
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).
hint |
is a comment that passes instructions to the optimizer on choosing an execution plan for the statement. For the syntax and description of hints, see "Hints" and Oracle8i Designing and Tuning for Performance. |
|
DML_table_expression_clause |
||
schema |
is the schema containing the table or view. If you omit schema, Oracle assumes the table or view is in your own schema. |
|
table | view |snapshot | subquery |
is 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: Oracle8i Data Cartridge Developer's Guide for more information on these routines. |
|
|
Issuing a All table or index space released by the deleted rows is retained by the table and index. |
|
|
Restrictions:
See Also: "ALTER SESSION". |
|
|
specifies that partition_name or subpartition_name is 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 |
is the complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see "Referring to Objects in Remote Databases". 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. |
|
with_clause |
restricts the subquery in one of the following ways: |
|
|
||
|
|
|
table_collection_expression |
informs Oracle that the collection value expression should be treated as a table. You can use a table_collection_expression to delete only those rows that also exist in another table. |
|
|
collection_expression |
is a subquery that selects a nested table column from table or view. |
|
Note: In earlier releases of Oracle, table_collection_expression was expressed as " |
|
where_clause |
deletes only rows that satisfy the condition. The condition can reference the table and can contain a subquery. See the syntax description in "Conditions". You can delete rows from a remote table or view only if you are using Oracle's distributed functionality. |
|
|
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. |
|
t_alias |
provides a correlation name for the table, view, subquery, or collection value to be referenced elsewhere in the statement. Table aliases are generally used in |
|
|
Note: This alias is required if the DML_query_expression_clause references any object type attributes or object type methods. |
|
returning_clause |
retrieves the rows affected by the |
|
|
You can use a returning_clause to return values from deleted columns, and thereby eliminate the need to issue a
You can also use For host binds, the datatype and size of the expression must be compatible with the bind variable. |
|
|
expr |
is any of the syntax descriptions in "Expressions". You must specify a column expression in the returning_clause for each variable in the data_item list. |
|
|
indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item list. |
|
data_item |
is a PL/SQL variable or bind variable that stores the retrieved expr value. |
|
Restrictions: |
The following statement deletes all rows from a table named TEMP_ASSIGN
.
DELETE FROM temp_assign;
The following statement deletes from the EMP
table all sales staff who made less than $100 commission last month:
DELETE FROM emp WHERE JOB = 'SALESMAN' AND COMM < 100;
The following statement has the same effect as the preceding example, but uses a subquery:
DELETE FROM (select * from emp) WHERE JOB = 'SALESMAN' AND COMM < 100;
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;
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 THE(SELECT projs FROM dept d WHERE d.dno = 123) AS p WHERE p.pno IN (123, 456) OR p.budgets > 456.78;
The following example removes rows from partition NOV98
of the SALES
table:
DELETE FROM sales PARTITION (nov98) WHERE amount_of_sale != 0;
The following example returns column SAL
from the deleted rows and stores the result in bind array :1:
DELETE FROM emp WHERE job = 'SALESMAN' AND COMM < 100 RETURNING sal INTO :1;
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|