Oracle7 Server SQL Reference

DELETE

Purpose

To remove rows from a table or from a view's base table.

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 any view's base table.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of the table or view or you must meet one of the following criteria:

If the creation label of the table or view is higher than your DBMS label, you must have READUP and WRITEUP system privileges.

If the creation label of your table or view is not comparable to your DBMS label, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

In addition, for each row to be deleted, your DBMS label must match the row's label or you must meet one of the following criteria:

If the row's label is higher than your DBMS label, you must have READUP and WRITEUP system privileges.

If the row's label is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the row's label is not comparable to your DBMS label, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

Keywords and Parameters

schema

is the schema containing the table or view. If you omit schema, Oracle7 assumes the table or view is in your own schema.

table view

is the name of a table from which the rows are to be deleted. If you specify view, Oracle7 deletes rows from the view's base table.

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 the section "Referring to Objects in Remote Databases" . You can only delete rows from a remote table or view if you are using Oracle7 with the distributed option.

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

subquery

is a subquery from which data is selected for deletion. For the syntax of subquery, see . Oracle executes the subquery and then uses the resulting rows as a table in the FROM clause. The subquery cannot query a table that appears in the same FROM clause as the subquery.

alias

is an alias assigned to the table, view or subquery. Aliases are generally used in DELETE statements with correlated queries.

WHERE

deletes only rows that satisfy the condition. The condition can reference the table and can contain a subquery. See the syntax description of condition . You can only delete rows from a remote table or view if you are using Oracle7 with the distributed option.

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

Usage Notes

All table and index space released by the deleted rows is retained by the table and index. You cannot delete from a view if the view's defining query contains one of the following constructs:

join

set operator

GROUP BY clause

group function

DISTINCT operator

Issuing a DELETE statement against a table fires any DELETE triggers defined on the table.

Example I

The following statement deletes all rows from a table named TEMP_ASSIGN.

DELETE FROM temp_assign

Example II

The following statement deletes from the employee table all sales staff who made less than $100 commission last month:

DELETE FROM emp
	WHERE JOB = 'SALESMAN'
	  AND COMM < 100

Example III

The following statement has the same effect as in Example II:

DELETE FROM (select * from emp)
	WHERE JOB = 'SALESMAN'
	  AND COMM < 100

Example IV

The following statement deletes all rows from the bank account table owned by the user BLAKE on a database accessible by the database link DALLAS: