DELETE command

This command deletes rows from a table

Syntax

DELETE FROM table_name (correlation_name)
       [ (WHERE search_condition) | 
       { (WHERE CURRENT OF cursor_name) } ]
Keyword Description
table_name Name of table or view from which to delete data rows
correlation_name Also called range variable or alias, provides an alternative name for the table whose name it follows; the definition lasts only for the duration of the statement. Correlation names are optional for base tables and views, but required for tables produced by subqueries
search_condition Criteria by which you identify rows on which you want to act

Use

This statement can be coded directly or in dynamic SQL, coded as a prepared statement, which is a statement whose text is generated at runtime. The DELETE statement removes rows from permanent base tables, views, or cursors. In the last two cases, the deletions are transferred to the base table from which the view or cursor extracts its data.

The WHERE CURRENT OF form is used for deletions from cursors. The row currently in the cursor is removed. This is called a positioned deletion. The WHERE predicate form is used for deletions from base tables or views. All rows that satisfy the predicate are removed at once. This is called a searched deletion. If the WHERE clause is absent, it is also a searched deletion, but all rows of the table or view are removed. The following restrictions apply to both types:

  • You must have DELETE privileges on the table to delete it
  • If the deletion is performed on a view or cursor, that view or cursor must be updatable
  • The current transaction mode cannot be read-only