DELETE (SQL)

This document covers the following topics:

Function
Syntax 1 - Searched DELETE
Syntax 2 - Positioned DELETE

Belongs to Function Group: Database Access and Update

See also DELETE - SQL in the Natural for DB2 part of the Database Management System Interfaces documentation:

Function

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.

Syntax 1 - Searched DELETE

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`	`RR` `RS` `CS`	[`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 `table-name`, in the result table of the `DELETE` statement, when it is nested in the `FROM` clause of a `SELECT` statement. For further details, see include-columns.
`SET assignment-clause`	SET Assignment Clause: Introduces the assignment of values to the included columns of the `include-columns` clause. See assignment clause of SQL `UPDATE` statement.
`WHERE search-condition`	WHERE Clause: This clause is used to specify the selection criteria for the rows to be deleted. If no `WHERE` clause is specified, the entire table is deleted.
`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 `EXPLAIN` output and trace records for this statement. The number is used as `QUERYNO` column in the `PLAN_TABLE` for the rows that contain information on this statement.

Syntax 2 - Positioned DELETE

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

[:]host-variable
integer

OF
                                 						ROWSET

For an explanation of the symbols used in the syntax diagram, see Syntax Symbols.

Syntax Element Description:

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 `(r)` notation is used to reference the statement which was used to select the row to be deleted. If no statement reference is specified, the `DELETE` statement is related to the innermost active processing loop in which a database record was selected.
`FOR ROW ... OF ROWSET`	FOR ROW … OF ROWSET Clause: This clause belongs to the SQL Extended Set. The optional `FOR ROW ... OF ROWSET` clause for positioned `SQL DELETE` statements specifies which row of the current rowset has to be deleted. It should only be specified if the `DELETE` statement is related to a `SELECT` statement which uses rowset positioning and which has column arrays in its `INTO` clause, see into-clause. If this clause is omitted, all rows of the current rowset are deleted.

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 (r) notation is used to reference the statement which was used to select the row to be deleted. If no statement reference is specified, the DELETE statement is related to the innermost active processing loop in which a database record was selected.

FOR ROW ... OF
                              						ROWSET

FOR ROW … OF ROWSET Clause:

This clause belongs to the SQL Extended Set.

The optional FOR ROW ... OF ROWSET clause for positioned SQL DELETE statements specifies which row of the current rowset has to be deleted. It should only be specified if the DELETE statement is related to a SELECT statement which uses rowset positioning and which has column arrays in its INTO clause, see into-clause. If this clause is omitted, all rows of the current rowset are deleted.