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 authorities:
- 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 authorities 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 authorities:
- 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--' '-| period-clause |-' |
+-nickname------------------------------+
+-ONLY--(--+-table-name-+--)------------+
| '-view-name--' |
'-(--fullselect--)----------------------'
>--+------------------------+--+---------------------+---------->
'-| correlation-clause |-' '-| include-columns |-'
>--+-----------------------+--+-------------------------+------->
'-| assignment-clause |-' '-WHERE--search-condition-'
>--+--------------+--------------------------------------------><
'-WITH--+-RR-+-'
+-RS-+
+-CS-+
'-UR-'
period-clause
|--FOR PORTION OF BUSINESS_TIME--FROM--value1--TO--value2-------|
include-columns
.-,----------------------.
V |
|--INCLUDE--(----column-name--data-type-+--)--------------------|
Syntax (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 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.
- 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.
- 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.
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 alternatives 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.
- 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