Restrictions on updates

Restrictions exist on the use of subqueries when you modify data. In particular, you cannot query the table that is being modified. You can refer to the present value of a column in an expression, as in the example that increments the unit_price column by 5 percent. You can also refer to a value of a column in a WHERE clause in a subquery, as in the example that updated the stock table, in which the items table is updated and items.stock_num is used in a join expression.

The need to update and query a table at the same time does not occur often in a well-designed database. (For more information about database design, see the Informix® Database Design and Implementation Guide.) However, you might want to update and query at the same time when a database is first being developed, before its design has been carefully thought through. A typical problem arises when a table inadvertently and incorrectly contains a few rows with duplicate values in a column that should be unique. You might want to delete the duplicate rows or update only the duplicate rows. Either way, a test for duplicate rows inevitably requires a subquery on the same table that you want to modify, which is not allowed in an UPDATE statement or DELETE statement. Modify data through SQL programs discusses how to use an update cursor to perform this kind of modification.