Delete

Valid in: SQL, ESQL, DBProc

The Delete statement deletes rows from the specified table that satisfy the search_condition in the where clause. If the where clause is omitted, the statement deletes all rows in the table. The result is a valid but empty table.

The Delete statement does not automatically recover the space in a table left by the deleted rows. However, if new rows are added later, the empty space can be reused. To recover lost space after deleting many rows from a table, modify the table. To delete all rows from a table, use modify...to truncated. For more information, see Modify.

Syntax

The Delete statement has the following formats:

Interactive and database procedure version:

[EXEC SQL] DELETE FROM [schema.]table_name [corr_name]
[WHERE search_condition];

table_name
Specifies the table for which the constraint is defined.

Note: A correlation name (corr_name) can be specified for the table for use in the search_condition.

Embedded non-cursor version:

[EXEC SQL] [REPEATED] DELETE FROM [schema.]table_name [corr_name]
[WHERE search_condition];

Embedded cursor version:

[EXEC SQL] DELETE FROM [schema.]table_name
WHERE CURRENT OF cursor_name;

Embedded Usage

In an embedded Delete statement, specify the cursor name with a string constant or a host language variable.

If the Delete statement does not delete any rows, the sqlcode variable in the SQLCA structure is set to 100. Otherwise, the sqlerrd(3) variable in the SQLCA structure contains the number of rows deleted.

There are two embedded versions of the delete statement: the first is similar to the interactive version of the statement, and the second is for use with cursors.

Non-Cursor Delete

The non-cursor version of the embedded SQL delete statement is identical to the interactive delete. Host language variables can be used to represent constant expressions in the search_condition but they cannot specify names of database columns or include any operators. The complete search condition can be specified using a host string variable.

To reduce the overhead required to execute a (non-cursor) delete repeatedly, specify the keyword repeated. The repeated keyword directs the DBMS Server to save the execution plan of the delete statement the first time the statement is executed, thereby improving subsequent executions of the same delete. The repeated keyword has no effect on the performance of cursor delete statements. The repeated delete cannot be specified as a dynamic SQL statement.

If the search_condition is dynamically constructed and the search_condition is changed after initial execution of the statement, the repeated option cannot be specified. The saved execution plan is based on the initial values in the search_condition and changes are ignored. This rule does not apply to simple variables used in search_conditions.

Cursor Delete

The cursor version deletes the row to which the specified cursor is pointing. If the cursor is not currently pointing at a row when the delete is executed, the DBMS Server generates an error.

To position the cursor to a row, issue a fetch statement. After a deletion, the cursor points to a position after the deleted row, but before the next row, if any.

If the cursor is opened for direct update, the deletion takes effect immediately. If the cursor is opened for deferred update, the deletion takes effect when the cursor is closed. If the cursor is opened for deferred update, a row cannot be deleted after it has been updated. If an attempt is made to do so, the DBMS Server returns an error indicating an ambiguous update operation.

Both the commit and rollback statements close all open cursors. A common programming error is to delete the current row of a cursor, commit the change and continue in a loop to repeat the process. This process fails because the first commit closes the cursor.

A cursor delete can be executed dynamically using the prepare and execute statements. However, a cursor delete can only be prepared after the referenced cursor is opened. The prepared cursor delete remains valid while the cursor is open. If the named cursor is closed and reopened, the corresponding delete statement must be reprepared. If an attempt is made to execute the delete statement associated with the previously open cursor, the DBMS Server returns an error.

In performing a cursor delete, make sure that certain conditions are met:

A cursor must be declared in the same file in which any delete statements referencing that cursor appear. This applies also to any cursors referenced in dynamic delete statement strings.
A cursor name in a dynamic delete statement must be unique among all open cursors in the current transaction.
The cursor stipulated in the delete must be open before the statement is executed.
The select statement of the cursor must not contain a distinct, group by, having, order by, or union clause.
The from clause of the delete and the from clause in the cursor's declaration must see the same database table.

Permissions

You must own the table or have delete permission. If the delete statement contains a where clause, select and delete permissions are required; otherwise, delete permission alone is sufficient.

Locking

The Delete statement locks pages as follows:

If row level locking is in effect, victim rows are X locked, with a weaker IX lock held on the data page. Also X locks are held on any secondary index pages affected by the delete.
If page level locking is in effect, the delete statement locks the pages containing the rows in the table that are evaluated against the where clause of the statement. If secondary indexes exist, delete also locks the pages in the secondary indexes that have pointers to the deleted rows.
If table level locking is in effect, a single lock on the base table is all that is needed. For a complete explanation of lock usage, see the Database Administrator Guide.

Related Statements

Example: Delete

The following example removes all employees who make over $35,000:

delete from employee where salary > 35000;