This document covers the following topics:
Belongs to Function Group: Database Access and Update
See also the following sections in the Database Management System Interfaces documentation:
DELETE - SQL in the Natural for DB2 part.
DELETE - SQL in the Natural for SQL/DS part.
DELETE - SQL in the Natural SQL Gateway part.
The SQL DELETE statement is used to delete either rows in a
table without using a cursor ("searched"
DELETE) or rows in a table to which a cursor is
positioned ("positioned"
DELETE).
Two different structures are possible.
The "searched" DELETE statement is a
stand-alone statement not related to any SELECT statement. With a single
statement you can delete zero, one, multiple or all rows of a table. The rows
to be deleted are determined by a search-condition
that is applied to the table. Optionally, the table name can be assigned a
correlation-name.
Note:
The number of rows that have actually been deleted with a
"searched" DELETE can be ascertained by using the
system variable *ROWCOUNT;
see System Variables documentation.
Common Set Syntax:
DELETE
FROM table-name
[correlation-name]
[WHERE
search-condition]
|
Extended Set Syntax:
DELETE
FROM table-name
[period-clause]
[correlation-name]
|
|||||||
[include-columns
[SET
assignment-clause]]
|
|||||||
[WHERE
search-condition]
|
|||||||
|
|
WITH
|
|
|
|
[SKIP LOCKED DATA] [QUERYNO
integer]
|
||
For an explanation of the symbols used in the syntax diagram, see Syntax Symbols.
Syntax Element Description:
| Syntax Element | Description | |
|---|---|---|
FROM
table-name
|
FROM Clause:
This clause specifies the table from which the rows are to be deleted. |
|
period-clause |
Period Clause:
Specifies that a period clause applies to the target of the update operation. For further information, see Period Clause in the section Basic Syntactical Items. |
|
correlation-name
|
Correlation Name:
Optional. The table name can be assigned a correlation-name. |
|
include-columns |
Include Columns Clause:
Optional. Specifies a set of columns that are included, along with
the columns of For further details, see include-columns. |
|
SET
assignment-clause |
SET Assignment Clause:
Introduces the assignment of values to the included columns of the
|
|
WHERE
search-condition
|
WHERE Clause:
This clause is used to specify the selection criteria for the rows to be deleted. If no |
|
WITH
|
WITH Isolation Level Clause:
This clause belongs to the SQL Extended Set. This clause enables the explicit specification of the isolation level used when locating the row to be deleted. |
|
CS |
Cursor Stability | |
RR |
Repeatable Read | |
RS |
Read Stability | |
SKIP LOCKED
DATA |
SKIP LOCKED DATA Clause:
This clause specifies that rows are skipped when incompatible locks are held on the row by other transactions. |
|
QUERYNO
integer
|
QUERYNO Clause:
This clause belongs to the SQL Extended Set. This clause explicitly specifies the number to be used in
|
|
The "positioned" DELETE statement always
refers to a cursor within a database loop. Therefore the table referenced by a
positioned DELETE statement must be the same as the one referenced
by the corresponding SELECT statement, otherwise an
error message is returned. A positioned DELETE cannot be used with
a non-cursor selection.
The functionality of the positioned DELETE statement
corresponds to that of the "native"
Natural DELETE statement.
Common Set Syntax:
DELETE FROM table-name
WHERE CURRENT OF CURSOR [(r)]
|
Extended Set Syntax:
DELETE FROM table-name
WHERE CURRENT OF CURSOR [(r)]
|
|
FOR
ROW |
|
|
OF
ROWSET |
|
||
For an explanation of the symbols used in the syntax diagram, see Syntax Symbols.
Syntax Element Description:
| Syntax Element | Description |
|---|---|
FROM
table-name WHERE CURRENT OF
CURSOR |
FROM Clause:
This clause specifies the table from which the rows are to be deleted. |
(r) |
Statement Reference:
The |
FOR ROW ... OF
ROWSET
|
FOR ROW … OF ROWSET Clause:
This clause belongs to the SQL Extended Set. The optional |
