DELETE statement

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

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.

Description

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

Identifies the object of the delete operation. The name must identify one of the following objects:

• A table or view that exists in the catalog at the current server
• A table or view at a remote server specified using a remote-object-name

The object must not be 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.

For additional restrictions related to temporal tables and use of a view or fullselect as the target of the delete operation, see Considerations for a system-period temporal table and Considerations for an application-period temporal table in the Notes section.

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.

period-clause

Specifies that a period clause applies to the target of the delete operation.

If the target of the delete operation is a view, the following conditions apply to the view:

• The FROM clause of the outer fullselect of the view definition must include a reference, directly or indirectly, to an application-period temporal table (SQLSTATE 42724M).
• An INSTEAD OF DELETE trigger must not be defined for the view (SQLSTATE 428HY).

FOR PORTION OF BUSINESS_TIME

Specifies that the delete only applies to row values for the portion of the period in the row that is specified by the period clause. The BUSINESS_TIME period must exist in the table (SQLSTATE 4274M). FOR PORTION OF BUSINESS_TIME must not be specified if the value of the CURRENT TEMPORAL BUSINESS_TIME special register is not NULL when the BUSTIMESENSITIVE bind option is set to YES (SQLSTATE 428HY).

FROM value1 TO value2

Specifies that the delete applies to rows for the period specified from value1 up to value2. No rows are deleted if value1 is greater than or equal to value2, or if value1 or value2 is the null value (SQLSTATE 02000).

For the period specified with FROM value1 TO value2, the BUSINESS_TIME period in a row in the target of the delete is in any of the following states:

• Overlaps the beginning of the specified period if the value of the begin column is less than value1 and the value of the end column is greater than value1.
• Overlaps the endof the specified period if the value of the end column is greater than or equal to value2 and the value of the begin column is less than value2.
• Is fully contained within the specified period if the value for the begin column for BUSINESS_TIME is greater than or equal to value1 and the value for the corresponding end column is less than or equal to value2.
• Is partially contained in the specified period if the row overlaps the beginning of the specified period or the end of the specified period, but not both.
• Fully overlaps the specified period if the period in the row overlaps the beginning and end of the specified period.
• Is not contained in the period if both columns of BUSINESS_TIME are less than or equal to value1 or greater than or equal to value2.

If the BUSINESS_TIME period in a row is not contained in the specified period, the row is not deleted. Otherwise, the delete is applied based on how the values in the columns of the BUSINESS_TIME period overlap the specified period as follows:

• If the BUSINESS_TIME period in a row is fully contained within the specified period, the row is deleted.
• If the BUSINESS_TIME period in a row is partially contained in the specified period and overlaps the beginning of the specified period:
  • The row is deleted.
  • A row is inserted using the original values from the row, except that the end column is set to value1.
• If the BUSINESS_TIME period in a row is partially contained in the specified period and overlaps the end of the specified period:
  • The row is deleted.
  • A row is inserted using the original values from the row, except that the begin column is set to value2.
• If the BUSINESS_TIME period in a row fully overlaps the specified period:
  • The row is deleted.
  • A row is inserted using the original values from the row, except that the end column is set to value1.
  • An additional row is inserted using the original values from the row, except that the begin column is set to value2.

value1 and value2

Each expression must return a value that has a date data type, timestamp data type, or a valid data type for a string representation of a date or timestamp (SQLSTATE 428HY). The result of each expression must be comparable to the data type of the columns of the specified period (SQLSTATE 42884). See the comparison rules described in Assignments and comparisons.

Each expression can contain any of the following supported operands (SQLSTATE 428HY):

• Constant
• Special register
• Variable. For details, refer to References to variables in Identifiers.
• Scalar function whose arguments are supported operands (though user-defined functions and non-deterministic functions cannot be used)
• CAST specification where the cast operand is a supported operand
• Expression using arithmetic operators and operands

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.

order-by-clause

Specifies the order of the rows for application of the offset-clause and fetch-clause. Specify an order-by-clause to ensure a predictable order for determining the set of rows to be deleted based on the offset-clause and fetch-clause. For details on the order-by-clause, see order-by-clause.

offset-clause

Limits the effect of the delete by skipping a subset of the qualifying rows. For details on the offset-clause, refer to offset-clause.

fetch-clause

Limits the effect of the delete to a subset of the qualifying rows. For details on the fetch-clause, refer to fetch-clause.

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.

SKIP LOCKED DATA

The SKIP LOCKED DATA clause specifies that rows are skipped when incompatible locks that would block the progress of the statement are held on the rows by other transactions. These rows can belong to any accessed table addressed in the statement, including tables accessed in a subquery. This clause applies when the isolation level is CS or RS and is ignored when an isolation level of UR or RR is in effect. It applies to row and block level locks.

Invocation

SKIP LOCKED DATA is ignored if it is specified when WITH RR or WITH UR. The default isolation level of the statement depends on the isolation of the package or plan with which the statement is bound, and whether the result table is read-only. If the default isolation level of the statement is Repeatable Read or Uncommitted Read, then SKIP LOCKED DATA is ignored.

NOWAIT / WAIT <time sec>

Attention: The following feature is available in Db2 11.5.6 and later versions.

The NOWAIT and WAIT clauses specify the number of seconds to wait for a lock before returning an error indicating that a lock cannot be obtained.

When using the WAIT clause, <time sec> is an integer between -1 and 32767.

Note: For NOWAIT and WAIT 0, locks are not waited for. If no lock is available at the time of the request, a -911 error is returned.

When a WAIT value of -1 is specified, lock timeout detection is turned off. In this situation a lock is waited for (if one is not available at the time of the request) until either of the following events occur:

• The lock is granted.
• A deadlock occurs.

Use of the NOWAIT and WAIT clauses overwrites the value of the LOCKTIMEOUT database configuration variable and the value of the CURRENT LOCK TIMEOUT special register for this delete statement. This means that adding the NOWAIT/WAIT clause with a wait time value of t has the same effect as executing the delete statement with a LOCKTIMEOUT value or CURRENT LOCK TIMEOUT value of t.

While the NOWAIT and WAIT clauses are not allowed for positioned updates and deletes, you can use them in the declaration of the cursor. When used in the cursor declaration, the specified wait time value is inherited by the statements that use this cursor.

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 preceding 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 syntax is supported for cross-product compatibility only. These alternatives are non-standard and should not be used.
  • The FROM keyword can be omitted.
• Considerations for a system-period temporal table: The target of the DELETE statement must not be a fullselect that references a view in the FROM clause followed by a period specification for SYSTEM_TIME if the view is defined with the WITH CHECK OPTION and the view definition includes a WHERE clause containing one of the following syntax elements (SQLSTATE 51046):
  • A subquery that references a system-period temporal table (directly or indirectly)
  • An invocation of an SQL routine that has a package associated with it
  • An invocation of an external routine with a data access indication other than NO SQL
  If the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value, an underlying target of the UPDATE statement must not be a system-period temporal table (SQLSTATE 51046), and the target of the DELETE statement must not be a view defined with the WITH CHECK OPTION if the view definition includes a WHERE clause containing one of the following syntax elements (SQLSTATE 51046):

  • A subquery that references a system-period temporal table (directly or indirectly)
  • An invocation of an SQL routine that has a package associated with it
  • An invocation of an external routine with a data access indication other than NO SQL

  If the DELETE statement has a search condition containing a correlated subquery that references historical rows (explicitly referencing the name of the history table name or implicitly through the use of a period specification in the FROM clause), the deleted rows that are stored as historical rows are potentially visible for delete operations for the rows subsequently processed for the statement.

  The mass delete algorithm is not used for a DELETE statement for a table defined as a system-period temporal table that does not contain a search condition.

• Considerations for a history table: When a row of a system-period temporal table is deleted, a historical copy of the row is inserted into the corresponding history table and the end timestamp of the historical row is captured in the form of a system determined value that corresponds to the time of the data change operation. The database manager assigns the value that is generated using a reading of the time-of-day clock during execution of the first data change statement in the transaction that requires a value to be assigned to the row begin or transaction start-ID column in a table, or a row in a system-period temporal table is deleted. The database manager ensures uniqueness of the generated values for an end column in a history table across transactions. The timestamp value might be adjusted to ensure that rows inserted into the history table have the end timestamp value greater than the begin timestamp value which can happen when a conflicting transaction is updating the same row in the system-period temporal table (SQLSTATE 01695). The database configuration parameter systime_period_adj must be set to Yes for this adjustment in the timestamp value to occur otherwise and error is returned (SQLSTATE 57062).

  For a delete operation, the adjustment only affects the value for the end column in the history table that corresponds to the row-end column in the associated system-period temporal table. Take these adjustments into consideration on subsequent references to the table when there is a search for the transaction start time in the row-begin column and row-end column for the SYSTEM_TIME period of the associated system-period temporal table.

• Considerations for an application-period temporal table: The target of the DELETE statement must not be a fullselect that references a view in the FROM clause followed by a period specification for BUSINESS_TIME if the view is defined with the WITH CHECK OPTION and the view definition includes a WHERE clause containing one of the following syntax elements (SQLSTATE 51046):
  • A subquery that references an application-period temporal table (directly or indirectly)
  • An invocation of an SQL routine that has a package associated with it
  • An invocation of an external routine with a data access indication other than NO SQL
  If the CURRENT TEMPORAL BUSINESS_TIME special register is set to a non-null value, the target of the DELETE statement must not be a view defined with the WITH CHECK option if the view definition includes a WHERE clause containing one of the following syntax elements (SQLSTATE 51046):

  • A subquery that references an application-period temporal table (directly or indirectly)
  • An invocation of an SQL routine that has a package associated with it
  • An invocation of an external routine with a data access indication other than NO SQL

  A DELETE statement for an application-period temporal table that contains a FOR PORTION OF BUSINESS_TIME clause indicates between which two points in time that the deletes are effective. When FOR PORTION OF BUSINESS_TIME is specified and the period value for a row, specified by the values of the row-begin column and row-end column, is only partially contained in the period specified from value1 up to value2, the row is deleted and one or two rows are automatically inserted to represent the portion of the row that is not deleted. New values are generated for each generated column in an application-period temporal table for each row that is automatically inserted as a result of a delete operation on the table. If a generated column is defined as part of a unique or primary key, parent key in a referential constraint, or unique index, it is possible that an automatic insert will violate a constraint or index in which case an error is returned.

  When an application-period temporal table is the target of an DELETE statement, the value in effect for the CURRENT TEMPORAL BUSINESS_TIME special register is not the null value, and the BUSTIMESENSITIVE bind option is set to YES, the following additional predicates are implicit:

        bt_begin <= CURRENT TEMPORAL BUSINESS_TIME
    AND bt_end > CURRENT TEMPORAL BUSINESS_TIME
  

  where bt_begin and bt_end are the begin and end columns of the BUSINESS_TIME period of the target table of the DELETE statement.
• Considerations for application-period temporal tables and triggers: When a row is deleted and the FOR PORTION OF BUSINESS_TIME clause is specified, additional rows may be implicitly inserted to reflect any portion of the row that was not deleted. Any existing delete triggers are activated for the rows deleted, and any existing insert triggers are activated for rows that are implicitly inserted.

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