DELETE Statement (delete_statement) 

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

Syntax

<delete_statement> ::=
  DELETE [FROM] <table_name> [<reference_name>] [KEY <key_spec>,...]
  [WHERE <search_condition>] [IGNORE TRIGGER] [NOWAIT]
| DELETE [FROM] <table_name> [<reference_name>] WHERE CURRENT OF <result_table_name> [NOWAIT]

Examples

SQL Tutorial, Inserting, Changing and Deleting Rows

Explanation

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

The current user must have been granted the DELETE privilege for the specified table. If the table name identifies a view table, the owner of the view table may also not have the DELETE privilege 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 tables are deleted.

Table name identifies a join view table: The following rows only 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.

<key_spec>, search_condition>

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

●     Optional sequence of key specifications and optional 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 containing the specified key values 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 in the result_table_name is specified, no rows are deleted if the cursor is not positioned on a row in the results table.

●     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, the following message appears: 100 row not found.

CURRENT OF

If CURRENT OF is specified, the table name in the FROM clause of the QUERY statement used to create the result_table_name must match 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.

You specify IGNORE TRIGGER to ensure that no DELETE trigger is executed by the DELETE statement.

NOWAIT

If you specify NOWAIT, the system does not wait for the lock to be released where a lock collision occurs, but 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.