SQL Reference

DELETE

The DELETE statement deletes rows from a table or view. Deleting a row from a view deletes the row from the table on which the view is based.

There are two forms of this statement:

• The Searched DELETE form is used to delete one or more rows (optionally determined by a search condition).
• The Positioned DELETE form is used to delete exactly one row (as determined by the current position of a cursor).

Invocation

A DELETE statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

To execute either form of this statement, the privileges held by the authorization ID of the statement must include at least one of the following:

• DELETE privilege on the table or view for which rows are to be deleted
• CONTROL privilege on the table or view for which rows are to be deleted
• SYSADM or DBADM authority.

To execute a Searched DELETE statement, the privileges held by the authorization ID of the statement must also include at least one of the following for each table or view referenced by a subquery:

• SELECT privilege
• CONTROL privilege
• SYSADM or DBADM authority.

When the package is precompiled with SQL92 rules ⁹⁰ and the searched form of a DELETE includes a reference to a column of the table or view in the search-condition, the privileges held by the authorization ID of the statement must also include at least one of the following:

• SELECT privilege
• CONTROL privilege
• SYSADM or DBADM authority.

When the specified table or view is preceded by the ONLY keyword, the privileges held by the authorization ID of the statement must also include the SELECT privilege for every subtable or subview of the specified table or view.

Group privileges are not checked for static DELETE statements.

Syntax

Searched DELETE:

>>-DELETE FROM----+-table-name-------------------+-------------->
                  +-view-name--------------------+
                  '-ONLY--(--+-table-name-+---)--'
                             '-view-name--'
 
>-----+---------------------------+----------------------------->
      | .-AS-.                    |
      '-+----+--correlation-name--'
 
>-----+--------------------------+-----------------------------><
      '-WHERE--search-condition--'
 

Positioned DELETE:

>>-DELETE FROM----+-table-name-------------------+-------------->
                  +-view-name--------------------+
                  '-ONLY--(--+-table-name-+---)--'
                             '-view-name--'
 
>----WHERE CURRENT OF--cursor-name-----------------------------><
 

Description

FROM table-name  or  view-name

Identifies the table or view from which rows are to be deleted. The name must identify a table or view that exists in the catalog, but it must not identify a catalog table, a catalog view, a summary table or a read-only view. (For an explanation of read-only views, see CREATE VIEW.)

If table-name is a typed table, rows of the table or any of its proper subtables may get deleted by the statement.

If view-name is a typed view, rows of the underlying table or underlying tables of the view's proper subviews may get deleted by the statement. If view-name is a regular view with an underlying table that is a typed table, rows of the typed table or any of its proper subtables may get deleted by the statement.

Only the columns of the specified table may be referenced in the WHERE clause. For a positioned DELETE, the associated cursor must also have specified the table or view in the FROM clause without using ONLY.

FROM ONLY (table-name)

Applicable to typed tables, the ONLY keyword specifies that the statement should apply only to data of the specified table and rows of proper subtables cannot be deleted by the statement. For a positioned DELETE, the associated cursor must also have specified the table in the FROM clause using ONLY. If table-name is not a typed table, the ONLY keyword has no effect on the statement.

FROM ONLY (view-name)

Applicable to typed views, the ONLY keyword specifies that the statement should apply only to data of the specified view and rows of proper subviews cannot be deleted by the statement. For a positioned DELETE, the associated cursor must also have specified the view in the FROM clause using ONLY. If view-name is not a typed view, the ONLY keyword has no effect on the statement.

correlation-name

May be used within the search-condition to designate the table or view. (For an explanation of correlation-name, see Language Elements.)

WHERE

Specifies a condition that selects the rows to be deleted. The clause can be omitted, a search condition specified, or a cursor named. If the clause is omitted, all rows of the table or view are deleted.

search-condition

Is any search condition as described in Search Conditions. Each column-name in the search condition, other than in a subquery must identify a column of the table or view.

The search-condition is applied to each row of the table or view and the deleted rows are those for which the result of the search-condition is true.

If the search condition contains a subquery, the subquery can be thought of as being executed each time the search condition is applied to a row, and the results used in applying the search condition. In actuality, a subquery with no correlated references is executed once, whereas a subquery with a correlated reference may have to be executed once for each row. If a subquery refers to the object table of a DELETE statement or a dependent table with a delete rule of CASCADE or SET NULL, the subquery is completely evaluated before any rows are deleted.

CURRENT OF cursor-name

Identifies a cursor that is defined in a DECLARE CURSOR statement of the program. The DECLARE CURSOR statement must precede the DELETE statement.

The table or view named must also be named in the FROM clause of the SELECT statement of the cursor, and the result table of the cursor must not be read-only. (For an explanation of read-only result tables, see DECLARE CURSOR.)

When the DELETE statement is executed, the cursor must be positioned on a row: that row is the one deleted. After the deletion, the cursor is positioned before the next row of its result table. If there is no next row, the cursor is positioned after the last row.

Rules

• If the identified table or the base table of the identified view is a parent, the rows selected for delete must not have any dependents in a relationship with a delete rule of RESTRICT, and the DELETE must not cascade to descendent rows that have dependents in a relationship with a delete rule of RESTRICT.

  If the delete operation is not prevented by a RESTRICT delete rule, the selected rows are deleted. Any rows that are dependents of the selected rows are also affected:

  • The nullable columns of the foreign keys of any rows that are their dependents in a relationship with a delete rule of SET NULL are set to the null value.
  • Any rows that are their dependents in a relationship with a delete rule of CASCADE are also deleted, and the above rules apply, in turn, to those rows.

  The delete rule of NO ACTION is checked to enforce that any non-null foreign key refers to an existing parent row after the other referential constraints have been enforced.

Notes

• If an error occurs during the execution of a multiple row DELETE, no changes are made to the database.
• Unless appropriate locks already exist, one or more exclusive locks are acquired during the execution of a successful DELETE statement. Issuing a COMMIT or ROLLBACK statement will release the locks. Until the locks are released by a commit or rollback operation, the effect of the delete operation can only be perceived by:
  • The application process that performed the deletion
  • Another application process using isolation level UR.
  The locks can prevent other application processes from performing operations on the table.
• If an application process deletes a row on which any of its cursors are positioned, those cursors are positioned before the next row of their result table. Let C be a cursor that is positioned before row R (as a result of an OPEN, a DELETE through C, a DELETE through some other cursor, or a searched DELETE). In the presence of INSERT, UPDATE, and DELETE operations that affect the base table from which R is derived, the next FETCH operation referencing C does not necessarily position C on R. For example, the operation can position C on R', where R' is a new row that is now the next row of the result table.
• SQLERRD(3) in the SQLCA shows the number of rows deleted from the object table after the statement executes. It does not include rows that were deleted as a result of a CASCADE delete rule. SQLERRD(5) in the SQLCA shows the number of rows affected by referential constraints and by triggered statements. It includes rows that were deleted as a result of a CASCADE delete rule and rows in which foreign keys were set to NULL as the result of a SET NULL delete rule. With regards to triggered statements, it includes the number of rows that were inserted, updated, or deleted. (For a description of the SQLCA, see Appendix B, SQL Communications (SQLCA).)
• If an error occurs that prevents deleting all rows matching the search condition and all operations required by existing referential constraints, no changes are made to the table and the error is returned.
• For any deleted row that includes currently linked files through DATALINK columns, the files are unlinked, and will be either restored or deleted, depending on the datalink column definition.

  An error may occur when attempting to delete a DATALINK value if the file server of value is no longer registered with the database server (SQLSTATE 55022).

  An error may also occur when deleting a row that has a link to a server that is unavailable at the time of deletion (SQLSTATE 57050).

Examples

Example 1:  Delete department (DEPTNO) 'D11' from the DEPARTMENT table.

  DELETE FROM DEPARTMENT
  WHERE DEPTNO = 'D11'

Example 2:  Delete all the departments from the DEPARTMENT table (that is, empty the table).

  DELETE FROM DEPARTMENT

⁹⁰

The package used to process the statement is precompiled using option LANGLEVEL with value SQL92E or MIA.