Rules for MERGE Request ON Clauses - Teradata Database

The following set of rules applies to MERGE request ON clauses. A MERGE request ON clause has:

One, and only one, primary condition

Zero, one, or many secondary conditions

A primary condition is the necessary condition for the MERGE request to execute, while secondary conditions are optional and are not required unless the application requires them.

Following are examples of primary and secondary ON clause conditions.

Suppose you create the following two tables and then submit the MERGE request that follows their definition.

     CREATE TABLE t1 (

       a1 INTEGER, 

       b1 INTEGER, 

       c1 INTEGER);

     CREATE TABLE t2 (

       a2 INTEGER, 

       b2 INTEGER, 

       c2 INTEGER);

     MERGE INTO t1

     USING t2

       ON a1=b2

     WHEN MATCHED THEN

       UPDATE SET b1=b2;

The predicate ON a1 = b2 is the primary, and only, condition in this MERGE request.

If the ON clause is modified to ON a1=b2 AND c1=c2, then c1=c2 is a secondary condition.