Informix Guide to SQL: Tutorial Modifying Data

The DELETE statement removes any row or combination of rows from a table. You cannot recover a deleted row after the transaction is committed. (Transactions are discussed under Interrupted Modifications. For now, think of a transaction and a statement as the same thing.)

When you delete a row, you must also be careful to delete any rows of other tables whose values depend on the deleted row. If your database enforces referential constraints, you can use the ON DELETE CASCADE option of the CREATE TABLE or ALTER TABLE statements to allow deletes to cascade from one table in a relationship to another. For more information on referential constraints and the ON DELETE CASCADE option, refer to Referential Integrity.

The DELETE statement specifies a table and usually contains a WHERE clause that designates the row or rows that are to be removed from the table. If the WHERE clause is left out, all rows are deleted. Do not execute the following statement:

DELETE FROM customer

Because this DELETE statement does not contain a WHERE clause, all rows from the customer table are deleted. If you attempt an unconditional delete using the DB-Access or the Relational Object Manager menu options, the program warns you and asks for confirmation. However, an unconditional delete from within a program can occur without warning.

The WHERE clause in a DELETE statement has the same form as the WHERE clause in a SELECT statement. You can use it to designate exactly which row or rows should be deleted. You can delete a customer with a specific customer number, as the following example shows:

DELETE FROM customer WHERE customer_num = 175

In this example, because the customer_num column has a unique constraint, you can ensure that no more than one row is deleted.

You can also choose rows that are based on nonindexed columns, as the following example shows:

DELETE FROM customer WHERE company = 'Druid Cyclery'

Because the column that is tested does not have a unique constraint, this statement might delete more than one row. (Druid Cyclery might have two stores, both with the same name but different customer numbers.)

To find out how many rows a DELETE statement affects, select the count of qualifying rows from the customer table for Druid Cyclery.

SELECT COUNT(*) FROM customer WHERE company = 'Druid Cyclery'

You can also select the rows and display them to ensure that they are the ones you want to delete.

Using a SELECT statement as a test is only an approximation, however, when the database is available to multiple users concurrently. Between the time you execute the SELECT statement and the subsequent DELETE statement, other users could have modified the table and changed the result. In this example, another user might perform the following actions:

• Insert a new row for another customer named Druid Cyclery
• Delete one or more of the Druid Cyclery rows before you insert the new row
• Update a Druid Cyclery row to have a new company name, or update some other customer to have the name Druid Cyclery

Although it is not likely that other users would do these things in that brief interval, the possibility does exist. This same problem affects the UPDATE statement. Ways of addressing this problem are discussed under Concurrency and Locks, and in greater detail in Chapter 9, Programming for a Multiuser Environment.

Another problem you might encounter is a hardware or software failure before the statement finishes. In this case, the database might have deleted no rows, some rows, or all specified rows. The state of the database is unknown, which is undesirable. To prevent this situation, use transaction logging, as Interrupted Modifications discusses.

When a row contains a column that is defined on a row type, you can use dot notation to specify that the only rows deleted are those that contain a specific field value. For example, the following statement deletes only those rows from the employee table in which the value of the city field in the address column is San Jose:

DELETE FROM employee
	WHERE address.city = 'San Jose'

In the preceding statement, the address column might be a named row type or an unnamed row type. The syntax you use to specify field values of a row type is the same.

When a row contains a column that is defined on a collection type, you can search for a particular element in a collection and delete the row or rows in which that element is found. For example, the following statement deletes rows in which the direct_reports column contains a collection with the element Baker:

DELETE FROM manager
	WHERE 'Baker' IN direct_reports

When you delete the rows of a supertable, the scope of the delete is a supertable and its subtables. Suppose you create a supertable person that has two subtables employee and sales_rep defined under it. The following DELETE statement on the person table can delete rows from all the tables person, employee, and sales_rep:

DELETE FROM person
	WHERE name ='Walker'

To limit a delete to rows of the supertable only, you must use the ONLY keyword in the DELETE statement. For example, the following statement deletes rows of the person table only:

DELETE FROM ONLY(person)
	WHERE name ='Walker'


Warning: Use caution when you delete rows from a supertable because the scope of a delete on a supertable includes the supertable and all its subtables.

The WHERE clause in a DELETE statement can be almost as complicated as the one in a SELECT statement. It can contain multiple conditions that are connected by AND and OR, and it might contain subqueries.

Suppose you discover that some rows of the stock table contain incorrect manufacturer codes. Rather than update them, you want to delete them so that they can be re-entered. You know that these rows, unlike the correct ones, have no matching rows in the manufact table. The fact that these incorrect rows have no matching rows in the manufact table allows you to write a DELETE statement such as the one in the following example:

DELETE FROM stock

	WHERE 0 = (SELECT COUNT(*) FROM manufact

	 	 WHERE manufact.manu_code = stock.manu_code)

The subquery counts the number of rows of manufact that match; the count is 1 for a correct row of stock and 0 for an incorrect one. The latter rows are chosen for deletion.

One way to develop a DELETE statement with a complicated condition is to first develop a SELECT statement that returns precisely the rows to be deleted. Write it as SELECT *; when it returns the desired set of rows, change SELECT * to read DELETE and execute it once more.

The WHERE clause of a DELETE statement cannot use a subquery that tests the same table. That is, when you delete from stock, you cannot use a subquery in the WHERE clause that also selects from stock.

The key to this rule is in the FROM clause. If a table is named in the FROM clause of a DELETE statement, it cannot also appear in the FROM clause of a subquery of the DELETE statement.

Instead of writing a subquery in the WHERE clause, in Enterprise Decision Server, you can use a delete join to join rows from various tables and delete these rows from a target table based on the join results.

As in the above example, suppose you discover that some rows of the stock table contain incorrect manufacturer codes. Rather than update them, you want to delete them so that they can be re-entered. You can use a delete join query such as the one in the following example:

DELETE FROM stock USING stock, manufact

	WHERE stock.maun_code != manufact.manu_code

All tables being joined should be listed in the using clause. Even if the target table is not being used for the join, it should be listed in the using clause. For more information on delete joins, see the DELETE statement in the Informix Guide to SQL: Syntax.