The primary condition in the ON clause must be an equality constraint. This is the minimum condition required for a valid ON clause predicate. Secondary conditions do not have this restriction.
For example, the following MERGE request is not valid because the primary ON clause condition, a1<a2
, is not an equality constraint.
MERGE INTO t1
USING t2
ON a1<a2
WHEN MATCHED THEN
UPDATE SET b1=b2;
The following MERGE request is valid because the primary condition in its ON clause, a1=a2
, is an equality constraint. The secondary condition b1<>b2
being a nonequality has no bearing on the validity of the request because any Θ operator is valid for a secondary condition.
USING t2
MERGE INTO t1
ON a1=a2 AND b1<>b2
WHEN MATCHED THEN
UPDATE SET b1=b2;