DELETE statement

Use the DELETE statement to delete one or more rows from a table, or to delete one or more elements from a collection variable of SPL or of .

Syntax

(1)
Notes:
Element Description Restrictions Syntax
alias Temporary name declared here for a table, view, or synonym. The AS keyword must precede alias if WHERE is the identifier of alias Identifier
condition Logical criteria that deleted rows must satisfy Cannot be a UDR nor a correlated subquery Condition
cursor_id Previously declared cursor Must have been declared FOR UPDATE Identifier
synonym, table, view Synonym, table, or updatable view with rows to be deleted The table or view (or synonym and the table or view to which it points) must exist Database Object Name

Usage

Use the DELETE statement to remove any of the following types of database objects or program objects:
  • A row in a table or in a view: a single row, a group of rows, or all rows
  • An element in a column of a collection data type
  • In a column of a named or unnamed ROW data type, a field, or all fields.

You can also use this statement to remove the values in one or more elements in or SPL collection variables or ROW variables.

To execute the DELETE statement, you must hold the DBA access privilege on the database, or the Delete access privilege on the table.

If you specify a view name, the view must be updatable. For an explanation of an updatable view, see Updating Through Views.

The DELETE statement cannot reference table objects that the CREATE EXTERNAL TABLE statement defined.

In a database with explicit transaction logging, any DELETE statement that you execute outside a transaction is treated as a single transaction.

In an ANSI-compliant database, data manipulation language (DML) statements are always in a transaction. You cannot execute a DELETE statement outside a transaction.

FROM clause

The FROM keyword that precedes the name of the target table is optional. To delete rows from a table named from, you can set the DELIMIDENT environment variable and use double quotation marks ( " ) to delimit "from":

DELETE "from";

Alternatively, you can qualify the name of the from table with the name of its owner: 

DELETE zelaine.from;

Your SQL code will be easier for humans to read and to maintain, however, if you avoid declaring SQL keywords as identifiers of tables, views, or other database objects.

WHERE clause

If you use DELETE without a WHERE clause (to specify either a condition or the active set of the cursor), all rows in the table are deleted: 
DELETE FROM tableZ;

It is typically more efficient, however, to use the TRUNCATE statement, rather than the DELETE statement, to remove all rows from a table.

In DB-Access, if you omit the WHERE clause while working at the SQL menu, DB-Access prompts you to verify that you want to delete all rows from a table. You do not receive a prompt if you execute DELETE within a command file.

Locking considerations

The database server locks each row affected by a DELETE statement within a transaction for the duration of the transaction. The locking granularity of the table can be PAGE level or ROW level.

Features that determine the locking granularity have this ascending order of precedence:
  • The DEF_TABLE_LOCKMODE configuration parameter can set the default granularity for table locks to PAGE or to ROW.
  • If the IFX_TABLE_LOCKMODE environment variable is set to PAGE or ROW, its setting overrides any DEF_TABLE_LOCKMODE default.
  • The LOCK MODE clause of the CREATE TABLE statement overrides any default locking granularity for the new table.
  • The LOCK MODE clause of the ALTER TABLE statement can reset the locking granularity to PAGE or to ROW for a table, overriding any of the settings above.
  • The LOCK TABLE statement always locks the entire table, overriding any locking granularity specification listed above for the specified table.
When the locking granularity of the table is ROW, the database server acquires one lock for each row affected by the DELETE statement.

If the number of rows affected is very large, and the lock mode is ROW, you might exceed the limits that your operating system places on the maximum number of simultaneous locks. If this occurs, you can either reduce the scope of the DELETE statement, or you can use LOCK TABLE statement to lock the table in exclusive mode before you execute the DELETE statement.