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:
NDB - DELETE - SQL in the Natural for DB2 part.
DELETE in the Natural for SQL/DS 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
)] [WHERE
search-condition]
|
|
WITH |
|
RR |
|
|
[QUERYNO
integer]
|
RS |
|||||||
CS
|
For an explanation of the symbols used in the syntax diagram, see Syntax Symbols.
Syntax Element Description:
| FROM table-name |
FROM Clause:
This clause specifies the table from which the rows are to be deleted. |
|---|---|
| correlation-name |
Optionally, the table name can be assigned a correlation-name. |
| 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 allows the explicit specification of the isolation level used when locating the row to be deleted. For detailed information, see WITH - Isolation Level in the corresponding section NDB - SELECT - Cursor-Oriented (in the Natural for DB2 part of the Database Management System Interfaces documentation). |
| 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:
| 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 |
