DB2 Version 9.7 for Linux, UNIX, and Windows

DELETE statement

The DELETE statement deletes rows from a table, nickname, or view, or the underlying tables, nicknames, or views of the specified fullselect. Deleting a row from a nickname deletes the row from the data source object to which the nickname refers. Deleting a row from a view deletes the row from the table on which the view is based if no INSTEAD OF trigger is defined for the delete operation on this view. If such a trigger is defined, the trigger will be executed instead.

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, view, or nickname from which rows are to be deleted
CONTROL privilege on the table, view, or nickname from which rows are to be deleted
DATAACCESS 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, view, or nickname referenced by a subquery:

SELECT privilege
CONTROL privilege
DATAACCESS authority

If the package used to process the statement is precompiled with SQL92 rules (option LANGLEVEL with a value of SQL92E or MIA), and the searched form of a DELETE statement 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
DATAACCESS authority

If 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.

If the target of the delete operation is a nickname, the privileges on the object at the data source are not considered until the statement is executed at the data source. At this time, the authorization ID that is used to connect to the data source must have the privileges required for the operation on the object at the data source. The authorization ID of the statement can be mapped to a different authorization ID at the data source.

Syntax

searched-delete:


>>-DELETE FROM--+-table-name-----------------+------------------>
                +-view-name------------------+   
                +-nickname-------------------+   
                +-ONLY--(--+-table-name-+--)-+   
                |          '-view-name--'    |   
                '-(--fullselect--)-----------'   

>--+------------------------+--+---------------------+---------->
   '-| correlation-clause |-'  '-| include-columns |-'   

>--+-----------------------+--+-------------------------+------->
   '-| assignment-clause |-'  '-WHERE--search-condition-'   

>--+--------------+--------------------------------------------><
   '-WITH--+-RR-+-'   
           +-RS-+     
           +-CS-+     
           '-UR-'     

include-columns

               .-,----------------------.      
               V                        |      
|--INCLUDE--(----column-name--data-type-+--)--------------------|

positioned-delete:


>>-DELETE FROM--+-table-name-----------------+------------------>
                +-view-name------------------+   
                +-nickname-------------------+   
                '-ONLY--(--+-table-name-+--)-'   
                           '-view-name--'        

>--+------------------------+--WHERE CURRENT OF--cursor-name---><
   '-| correlation-clause |-'                                  

correlation-clause

   .-AS-.                                            
|--+----+--correlation-name--+-------------------+--------------|
                             '-(--column-name--)-'

Description

FROM table-name, view-name, nickname, or (fullselect)

Identifies the object of the delete operation. 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 system-maintained materialized query table, or a read-only 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.

If the object of the delete operation is a fullselect, the fullselect must be deletable, as defined in the "Deletable views" Notes item in the description of the CREATE VIEW statement.

Only the columns of the specified table can 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-clause

Can be used within the search-condition to designate a table, view, nickname, or fullselect. For a description of correlation-clause, see "table-reference" in the description of "Subselect".

include-columns

Specifies a set of columns that are included, along with the columns of table-name or view-name, in the intermediate result table of the DELETE statement when it is nested in the FROM clause of a fullselect. The include-columns are appended at the end of the list of columns that are specified for table-name or view-name.

INCLUDE: Specifies a list of columns to be included in the intermediate result table of the DELETE statement.
column-name: Specifies a column of the intermediate result table of the DELETE statement. The name cannot be the same as the name of another include column or a column in table-name or view-name (SQLSTATE 42711).
data-type: Specifies the data type of the include column. The data type must be one that is supported by the CREATE TABLE statement.

assignment-clause

See the description of assignment-clause under the UPDATE statement. The same rules apply. The include-columns are the only columns that can be set using the assignment-clause (SQLSTATE 42703).

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

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, view, or nickname, 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, view, or nickname 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.

WITH

Specifies the isolation level used when locating the rows to be deleted.

RR: Repeatable Read
RS: Read Stability
CS: Cursor Stability
UR: Uncommitted Read

The default isolation level of the statement is the isolation level of the package in which the statement is bound. The WITH clause has no effect on nicknames, which always use the default isolation level of the statement.

Rules

Triggers: DELETE statements may cause triggers to be executed. A trigger may cause other statements to be executed, or may raise error conditions based on the deleted rows. If a DELETE statement on a view causes an INSTEAD OF trigger to fire, referential integrity will be checked against the updates performed in the trigger, and not against the underlying tables of the view that caused the trigger to fire.
Referential integrity: 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.
Security policy: If the identified table or the base table of the identified view is protected with a security policy, the session authorization ID must have the label-based access control (LBAC) credentials that allow:
- Write access to all protected columns (SQLSTATE 42512)
- Read and write access to all of the rows that are selected for deletion (SQLSTATE 42519)

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 that qualified for the delete operation. In the context of an SQL procedure statement, the value can be retrieved using the ROW_COUNT variable of the GET DIAGNOSTICS statement. 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 the null value 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.
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 nicknames, the external server option iud_app_svpt_enforce poses an additional limitation. Refer to the Federated documentation for more information.
For some data sources, the SQLCODE -20190 may be returned on a delete against a nickname because of potential data inconsistency. Refer to the Federated documentation for more information.
Syntax alternatives: The following are supported for compatibility with previous versions of DB2® and with other database products. These alternatives are non-standard and should not be used.
- The FROM keyword can be omitted.

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

Example 3: Delete from the EMPLOYEE table any sales rep or field rep who didn't make a sale in 1995.

   DELETE FROM EMPLOYEE
     WHERE LASTNAME NOT IN
       (SELECT SALES_PERSON
         FROM SALES
         WHERE YEAR(SALES_DATE)=1995)
         AND JOB IN ('SALESREP','FIELDREP')

Example 4: Delete all the duplicate employee rows from the EMPLOYEE table. An employee row is considered to be a duplicate if the last names match. Keep the employee row with the smallest first name in lexical order.

   DELETE FROM
     (SELECT ROWNUMBER() OVER (PARTITION BY LASTNAME ORDER BY FIRSTNME)
       FROM EMPLOYEE) AS E(RN)
       WHERE RN > 1