Using the USING or FROM Keyword to Introduce a Join Condition (XPS)

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 database server performs the query as if you specified no list of tables.

You can use a second FROM keyword to introduce the list of tables, but your code will be easier to read if you use the USING keyword instead.

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 implies that the SELECT statement that defines the view cannot contain any of the following syntax elements:

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

The next example deletes the rows from the lineitem table whose corresponding rows in the order table show 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.

In ESQL/C, 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.