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 [(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. |
|
correlation-name
|
Correlation Name:
Optionally, the table name can be assigned a correlation-name. |
|
include-columns |
Include Columns Clause:
Optionally,
specifies a set of columns that are included, along with the columns of
|
|
SET
assingment-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 "normal"
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 |
|
[:]host-variable |
|
OF
ROWSET |
|
|
| integer | ||||||||
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 |
