>>-DELETE--+----------------------------------------+----------->
           |                          (1)  (2) (3)  |
           '-| Optimizer Directives |---------------'
 
>--+-FROM---------+--------------------------------------------->
   |  (2)    (3)  |
   '--------------'
 
>--+-table----------------------------------------------+------->
   +-view-----------------------------------------------+
   +-synonym--------------------------------------------+
   |                                         (2) (3)    |
   '---+-ONLY--+-(table )--+---------------+------------'
       |       '-(synonym)-'               |
       |                              (4)  |
       '-| Collection-Derived Table |------'
 
>--+--------------------------------------------------------------------+-><
   |                                                               (4)  |
   +-+-------------------------------------+--WHERE--| Condition |------+
   | |            .-,-----------.          |                            |
   | |            V             | (2) (5)  |                            |
   | '-+-USING-+----+-synonym-+-+----------'                            |
   |   '-FROM--'    +-table---+                                         |
   |                +-view----+                                         |
   |                '-alias---'                                         |
   |                            (6)                                     |
   '-WHERE CURRENT OF cursor_id-----------------------------------------'
 

1. See Optimizer Directives
2. Informix extension
3. Dynamic Server only
4. See Condition
5. Extended Parallel Server only
6. ESQL/C and Stored Procedure Language only

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

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

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

The database server locks each row affected by a DELETE statement within a transaction for the duration of the transaction. The type of lock that the database server uses is determined by the lock mode of the table, as set by a CREATE TABLE or ALTER TABLE statement, as follows:

• If the lock mode is ROW, the database server acquires one lock for each row affected by the delete.
• In Extended Parallel Server, if the lock mode is PAGE, the database server acquires one lock for each page affected by the delete.

If the number of rows affected is very large and the lock mode is ROW, you might exceed the limits 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 lock the table in exclusive mode before you execute the statement.

4If you use DELETE without a WHERE clause (to specify either 4a condition or the active set of the cursor), all rows in the table are deleted. 4It is typically more efficient, however, to use the TRUNCATE statement, rather 4than 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 run execute DELETE within a command file.

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

On Dynamic Server, the FROM keyword that immediately follows DELETE can be omitted if the DELIMIDENT environment variable has been set.