DELETE (SQL)

#### Search Results

Search

# 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]
[`FETCH FIRST` row-limit]
| `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: 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: Specifies the selection criteria for the rows to be deleted. If no `WHERE` clause is specified, the entire table is deleted.
`FETCH FIRST` | FETCH FIRST Clause: Limits the effects of the `DELETE` statement to a subset of qualifying rows. It corresponds to the `FETCH FIRST` clause of the `SELECT` statement described in `FETCH FIRST` row-limit.
`WITH` | WITH Isolation Level Clause: Enables the explicit specification of the isolation level used when locating the row to be deleted. This clause belongs to the SQL Extended Set.
`CS` | Cursor Stability
`RR` | Repeatable Read
`RS` | Read Stability
`SKIP LOCKED DATA` | SKIP LOCKED DATA 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
--- | ---
`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.

### On this page

- Function
- Syntax 1 - Searched DELETE
- Syntax 2 - Positioned DELETE