DELETE Statement (delete_statement)

The DELETE statement (delete_statement) deletes rows from a table.

Structure

Syntax Syntax

<delete_statement> ::=
  DELETE [FROM] <table_name>
    [<reference_name>]
    [KEY <key_spec>,...] <!  This SQL clause is
          no longer recommended to be used and
          might be removed from future versions.  !>
    [WHERE <search_condition>] 
    [IGNORE TRIGGER]    [NOWAIT]
| DELETE [FROM] <table_name>
    [<reference_name>]
    WHERE CURRENT OF <result_table_name>
    [NOWAIT]

End of the code.

Examples

SQL Tutorial, Inserting, Changing and Deleting Rows

Explanation

The table name must identify an existing base table, view table, or synonym.

The current database user must have the DELETE privilege for the specified table. If the table name identifies a view table, it is possible that the owner of the view table does not have the DELETE privilege either, because the view table is not updateable.

<table_name>

Table name identifies a view table: The rows of the underlying base tables for the view table are deleted.

Table name identifies a join view table: Only the following rows are deleted:

Rows in the key table of the join view table
Rows in underlying base tables for the view table that have a 1:1 relationship with the key table.

Which rows are deleted?

The following specifications determine which rows in the table are deleted:

Optional key specification (key_spec) and optional search condition (search_condition)
Key specification and no search condition:

A row with the specified key values already exists. This row is deleted. No rows are deleted if a row with the specified key values does not exist.

Key specification and a search condition:

A row with the specified key values already exists. The search condition is applied to this row. If the search condition is satisfied, then the row is deleted. No rows are deleted if a row with the specified key values does not exist or if a search condition applied to a row is not satisfied.

No key specification and a search condition:

The search condition is applied to each row in the specified table. All rows for which the search condition is satisfied are deleted.
When using CURRENT OF so that the cursor position within the result table result_table_name is specified: If the cursor is not positioned on a row in the results table, no rows are deleted.
If none of the above are specified, all rows in the specified table are deleted.
If no row is found that satisfies the conditions defined by the optional clauses, a message to this effect appears.

Key Specification (key_spec)

Caution Caution

This SQL clause is no longer recommended to be used and might be removed from future versions.

End of the caution.

CURRENT OF

If CURRENT OF is specified, the table name in the FROM clause of the QUERY statement used to construct the result table result_table_name must be identical to the table name in the DELETE statement.

If CURRENT OF is specified and the cursor is positioned on a row in the result table, the corresponding row is deleted. This is the row in the table specified in the FROM clause of the QUERY statement used to form the row of the results table. It is essential that the results table was specified using FOR UPDATE. Afterwards, the cursor is positioned after the row in the results table. It is impossible to predict whether or not the updated values in the corresponding row are visible the next time this row in the results table is accessed.

IGNORE TRIGGER

If triggers are defined for base tables from which rows are to be deleted using the DELETE statement, for execution after a DELETE statement, these are executed accordingly. The DELETE statement will fail if one of these triggers fails.

If you specify IGNORE TRIGGER, the DELETE statement does not execute a DELETE trigger. IGNORE TRIGGER must be specified within a CREATE TRIGGER statement only.

NOWAIT

If you specify NOWAIT, the system does not wait for the lock to be released where a lock collision occurs. Instead, it returns an error message immediately.

DELETE Rule

For each row deleted in the course of the DELETE statement which originates from a referenced_table of at least one referential CONSTRAINT definition, one of the following actions is carried out – depending on the DELETE rule of the referential CONSTRAINT definition:

DELETE CASCADE: All matching rows in the corresponding foreign key table are deleted.
DELETE RESTRICT: If there are matching rows in the corresponding foreign key table, the DELETE statement fails.
DELETE SET NULL: The NULL value is assigned to the respective foreign key columns of all matching rows in the corresponding foreign key table.
DELETE SET DEFAULT: The DEFAULT value set by the DEFAULT specification is assigned to the respective foreign key columns for all matching rows in the corresponding foreign key table.

More Information

CREATE VIEW Statement

Privileges: Overview

Data Manipulation