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 Informix® ESQL/C.
Syntax
| 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
- 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 Informix® ESQL/C 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
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.
- 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.
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.