The primary condition must be conjunctive (ANDed) with any secondary conditions specified in the ON clause. The secondary condition terms can be disjunctive (ORed) among themselves, but not with the primary condition.
For example, consider the following target and source table definitions:
CREATE TABLE t1 (
a1 INTEGER,
b1 INTEGER,
c1 INTEGER);
CREATE TABLE t2 (
a2 INTEGER,
b2 INTEGER,
c2 INTEGER);
The following MERGE request is valid because the target table primary index equality
expression a1=a2
, which is the primary condition, is ANDed with the secondary condition b1=b2
.
MERGE INTO t1
USING t2
ON a1=a2 AND b1=b2
WHEN MATCHED THEN
UPDATE SET c1=c2;
The following MERGE request is not valid because the target table primary index equality expression a1=a2
is ORed with the secondary condition b1=b2
.
MERGE INTO t1
USING t2
ON a1=a2 OR b1=b2
WHEN MATCHED THEN
UPDATE SET c1=c2;
However, the ON clause secondary conditions can contain ORed terms.
For example, the following MERGE request is valid because its primary condition a1=a2
is ANDed with the secondary condition (b1=b2 OR c1=c2
), and the disjunction is contained entirely within the secondary condition:
MERGE INTO t1
USING t2
ON a1=a2 AND (b1=b2 OR c1=c2)
WHEN MATCHED THEN
UPDATE SET c1=c2;