The following examples show the proper and improper specification of ON clause conditions in a MERGE request.
Consider the following table definitions, with t1 being the target relation and t2 being the source relation for all the examples that follow:
CREATE TABLE t1 ( a1 INTEGER, b1 INTEGER, c1 INTEGER) PRIMARY INDEX (a1); CREATE TABLE t2 ( a2 INTEGER, b2 INTEGER, c2 INTEGER) PRIMARY INDEX (a2);
The following example is correct because the primary index equality constraint a1=a2 is ANDed with the other specified condition in the request, b1=b2:
MERGE INTO t1 USING t2 ON a1=a2 AND b1=b2 WHEN MATCHED THEN UPDATE SET c1=c2 WHEN NOT MATCHED THEN INSERT (a2, b2, c2);
The following example is correct because the primary index equality constraint a1=a2 is ANDed with the other specified condition in the request c1+c2=1 OR b1+b2=1.
Even though the second condition is internally disjunctive, the result it evaluates to is ANDed with the primary index condition.
MERGE INTO t1 USING t2 ON a1=a2 AND (c1+c2=1 OR b1+b2=1) WHEN MATCHED THEN UPDATE SET c1=c2;
The following example is not valid. It aborts and returns an error message to the requestor because the primary index equality constraint a1=a2 is ORed with the other specified condition in the request, c1=c2.
The primary index equality constraint, as well as the partitioning column equality constraint if the target table has a partitioned primary index, must always be ANDed to any other conditions you specify in the ON clause.
MERGE INTO t1 USING t2 ON a1=a2 OR b1=b2 WHEN MATCHED THEN UPDATE SET c1=c2;