The Delete clause of MERGE

Instead of writing a subquery in the WHERE clause, you can use the MERGE statement to join rows from a source tables and a target table, and then delete from the target the rows that match the join condition. (The source table in a Delete MERGE can also be a collection-derived table whose rows are the result of a query that joins other tables and views, but in the example that follows, the source is a single table.)

As in the previous 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 the MERGE statement that specifies stock as the target table, manufact as the source table, a join condition in the ON clause, and with the Delete clause for the stock rows with incorrect manufacturer codes, as in the following example:

MERGE INTO stock USING manufact
   ON stock.manu_code != manufact.manu_code
WHEN MATCHED THEN DELETE;

In this example, all the rows of the stock table for which the join condition in the ON clause is satisfied will be deleted. Here the inequality predicate in the join condition (stock.manu_code != manufact.manu_code) evaluates to true for the rows of stock in which the manu_code column value is not equal to any manu_code value in the manufact table.

The source table that is being joined to the target table must be listed in the USING clause.

The MERGE statement can also update rows of the target table, or insert data from the source table into the target table, according to whether or not the row satisfies the condition that the ON clause specifies for joining the target and source tables. A single MERGE statement can also combine both DELETE and INSERT operations, or can combine both UPDATE and INSERT operations without deleting any rows. The source table is unchanged by the MERGE statement. For more information on the syntax and restrictions for Delete merges, Insert merges, and Update merges, see the description of the MERGE statement in the HCL OneDB™ Guide to SQL: Syntax.