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

## 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 [correlation-name]
[`WHERE` search-condition]
| `WITH` |  | `RR` `RS` `CS` |  |  | [`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.
`correlation-name` | Correlation Name: Optional. The table name can be assigned a correlation-name.
`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.
`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. It is only valid against Db2 databases. When used against other databases, it will cause runtime errors.
`CS` | Cursor Stability
`RR` | Repeatable Read
`RS` | Read Stability
`QUERYNO integer` | QUERYNO Clause: This clause belongs to the SQL Extended Set. This clause is not currently supported and will be ignored.

## 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.

`DELETE FROM` table-name `WHERE CURRENT OF CURSOR` [(r)]

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