Informix Guide to SQL: Syntax SQL Statements

Use the DELETE statement to delete one or more rows from a table, or one or more elements in an SPL or ESQL/C collection variable.

If you use the DELETE statement without a WHERE clause (either to introduce a condition or to indicate the active set of the cursor), the database server deletes all the rows in the table.

If you use the DELETE statement outside a transaction in a database that uses transactions, each DELETE statement that you execute is treated as a single transaction.

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 Enterprise Decision 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.

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

If you omit the WHERE clause while you are working within 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 the DELETE statement within a command file.

In an ANSI-compliant database, statements are always in an implicit transaction. Therefore, you cannot have a DELETE statement outside a transaction.

If you use the DELETE statement to remove rows of a supertable, rows from both the supertable and its subtables can be deleted. To delete rows from the supertable only, you must use the ONLY keyword prior to the table name, as the following example shows:

DELETE FROM ONLY(super_tab)
WHERE name = "johnson"


Warning: If you use the DELETE statement on a supertable without the ONLY keyword and without a WHERE clause, all rows of the supertable and its subtables are deleted.

You cannot specify the ONLY keyword if you plan to use the WHERE CURRENT OF clause to delete the current row of the active set of a cursor. 

When you use the ON DELETE CASCADE option of the REFERENCES clause on either the CREATE TABLE or ALTER TABLE statement, you specify that you want deletes to cascade from one table to another. For example, in the stores_demo database, the stock table contains the column stock_num as a primary key. The catalog and items tables each contain the column stock_num as foreign keys with the ON DELETE CASCADE option specified. When a delete is performed from the stock table, rows are also deleted in the catalog and items tables, which are referred through the foreign keys.

To have deletes cascade to a table that has a referential constraint on a parent table, you need the DELETE privilege only on the parent table that you mention in the DELETE statement.

If a delete without a WHERE clause is performed on a table that one or more child tables reference with cascading deletes, the database server deletes all rows from that table and from any affected child tables.

For an example of how to create a referential constraint that uses cascading deletes, see Using the ON DELETE CASCADE Option.

If you have a parent table with two child tables that reference it, one child with cascading deletes specified and one child without cascading deletes, and you attempt to delete a row from the parent table that applies to both child tables, the delete statement fails, and no rows are deleted from either the parent or child tables.

You cannot use a child table in a correlated subquery to delete a row from a parent table.

During deletes, the database server places locks on all qualifying rows of the referenced and referencing tables.

You must turn logging on when you perform the deletes. If logging is turned off in a database, even temporarily, deletes do not cascade because if logging is turned off, you cannot roll back any actions. For example, if a parent row is deleted, and the system fails before the child rows are deleted, the database will have dangling child records, which violates referential integrity. However, when logging is turned back on, subsequent deletes cascade.

Use the WHERE keyword to introduce a condition that separates from the table one or more rows that you want to delete. The WHERE conditions are the same as the conditions in the SELECT statement. For example, the following statement deletes all the rows of the items table where the order number is less than 1034:

DELETE  FROM  items

	WHERE  order_num  <  1034

If you include a WHERE clause that selects all rows in the table, DB-Access gives no prompt and deletes all rows.

If you are deleting from a supertable in a table hierarchy, a subquery in the WHERE clause cannot reference a subtable.

If you are deleting from a subtable in a table hierarchy, a subquery in the WHERE clause can reference the supertable if it references only the supertable. That is, the subquery must use the SELECTFROM ONLY (supertable)... syntax.

Use the WHERE CURRENT OF clause to delete the current row of the active set of a cursor.

When you use the WHERE CURRENT OF clause, the DELETE statement removes the row of the active set at the current position of the cursor. After the deletion, no current row exists; you cannot use the cursor to delete or update a row until you reposition the cursor with a FETCH statement.

You access the current row of the active set of a cursor with an update cursor. Before you can use the this clause, you must create a cursor with the FOREACH statement (SPL) or the DECLARE statement with the FOR UPDATE clause (ESQL/C).

All select cursors are potentially update cursors in an ANSI-compliant database. You can use this clause with any select cursor.

You cannot use this clause if you are selecting from only one table in a table hierarchy. That is, you cannot use this option if you use the ONLY keyword.

You use this clause to delete an element from a collection. However, you actually delete the current row of the collection derived table that a collection variable holds. For more information, see Collection Derived Table.

If you want to delete information from a table based on information contained in one or more other tables, use the USING keyword or a second FROM keyword to introduce the list of tables that you want to join in the WHERE clause.

When you use this syntax, the WHERE clause can include any complex join.

If you do not list a join in the WHERE clause, the database server ignores the tables listed after the introductory keyword (either USING or FROM). That is, the query performs as if the list of tables was not included.

Although you can use a second FROM keyword to introduce the list of tables, Informix recommends that you use the USING keyword for more readable code.

When you use a delete join, the entire operation occurs as a single transaction. For example if a delete join query is supposed to delete 100 rows and an error occurs after the 50th row, the first 50 rows that are already deleted will reappear in the table.

When you introduce a list of tables that you want to join in the WHERE clause, the following restrictions for the DELETE statement exist:

• You must list the target table (the one from which rows are to be deleted) and any table that will be part of a join after the USING (or second from) keyword.
• The WHERE clause cannot contain outer joins.
• The target table cannot be a static or external table.
• The statement cannot contain cursors.
• If the target table is a view, the view must be updatable.

That is, the SELECT statement that defines the view cannot contain any of the following syntax:

• Aggregate expressions
• UNIQUE or DISTINCT
• UNION
• GROUP BY

The following example deletes those rows from the lineitem table whose corresponding rows in the order table show that nothing was ordered (that is, a qty of less than one).

DELETE FROM lineitem
USING order o, lineitem l
WHERE o.qty < 1 AND o.order_num = l.order_num

A delete join makes it easier to incorporate new data into a database. For example, you can:

1. Store new values in a temporary table.
2. Use a delete join (DELETE...USING statement) to remove any records from the temporary table that already exist in the table into which you want to insert the new records.
3. Insert the remaining records into the table.

In addition, you can use this syntax instead of deleting from the results of a SELECT statement that includes a join.

Some opaque data types require special processing when they are deleted. For example, if an opaque data type contains spatial or multirepresentational data, it might provide a choice of how to store the data: inside the internal structure or, for very large objects, in a smart large object.

This processing is accomplished by calling a user-defined support function called destroy(). When you use the DELETE statement to delete a row that contains one of these opaque types, the database server automatically invokes the destroy() function for the type. The destroy() support function can decide how to remove the data, regardless of where it is stored. For more information on the destroy() support function, see Extending Informix Dynamic Server 2000.

When a row contains a column that is a collection data type (LIST, MULTISET, or SET), you can search for a particular element in the collection, and delete the row or rows in which the element is found. For example, the following statement deletes any rows from the new_tab table in which the set_col column contains the element jimmy smith:

DELETE FROM new_tab
WHERE 'jimmy smith' IN set_col

You can also use a collection variable to delete values in a collection column. With a collection variable you can delete one or more individual elements in a collection. For more information, see Collection Derived Table.

If you delete from a table in an ANSI-compliant database with a DELETE statement that contains a WHERE clause and no rows are found, that database server issues a warning. You can detect this warning condition in either of the following ways:

• The GET DIAGNOSTICS statement sets the RETURNED_SQLSTATE field to the value 02000. In an SQL API application, the SQLSTATE variable contains this same value.
• In an SQL API application, the sqlca.sqlcode and SQLCODE variables contain the value 100.

The database server also sets SQLSTATE and SQLCODE to these values if the DELETE... WHERE... is a part of a multistatement prepare and the database server returns no rows.

In a database that is not ANSI compliant, the database server does not return a warning when it finds no matching rows for a WHERE clause in a DELETE statement. The SQLSTATE code is 00000 and the SQLCODE code is zero (0). However, if the DELETEWHERE is a part of a multistatement prepare, and no rows are returned, the database server does issue a warning. It sets SQLSTATE to 02000 and SQLCODE value to 100.

Related Statements: DECLARE, FETCH, GET DIAGNOSTICS, INSERT, OPEN, SELECT, and UPDATE

For discussions of the DELETE statement, SPL routines, statement modification, cursors, and the SQLCODE code, see the Informix Guide to SQL: Tutorial.

For information on how to access row and collections with ESQL/C host variables, see the chapter on complex data types in the Informix ESQL/C Programmer's Manual.

For a discussion of the GLS aspects of the DELETE statement, see the Informix Guide to GLS Functionality.