Using a Delete Join (XPS)

Instead of writing a subquery in the WHERE clause, in Extended Parallel 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.manu_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 IBM Informix: Guide to SQL Syntax.