If the primary index value results from the evaluation of an expression, then that expression cannot reference any column in the target table.
Consider the following target and source table definitions:
CREATE TABLE t1 (
x1 INTEGER,
y1 INTEGER,
z1 INTEGER)
PRIMARY INDEX(x1,y1);
CREATE TABLE t2 (
x2 INTEGER,
y2 INTEGER,
z2 INTEGER)
PRIMARY INDEX(x2,y2);
The following MERGE request is valid because it specifies equality conditions on each of the primary index columns of target table t1, the columns x1 and y1:
MERGE INTO t1
USING t2
ON x1=z2 AND y1=y2
WHEN MATCHED THEN
UPDATE SET z1=10
WHEN NOT MATCHED THEN
INSERT (z2, y2, x2);
The following MERGE request is valid because it specifies equality conditions on each
of the primary index columns of target table t1 and because the expressions on the RHS of those conditions, z2+10 and y2+20, are also specified for the primary index columns of t1 in the INSERT specification of the request:
MERGE INTO t1
USING t2
ON x1=z2+10 AND y1=y2+20
WHEN MATCHED THEN
UPDATE SET z1=10
WHEN NOT MATCHED THEN
INSERT (x1, y1, z1) VALUES (z2+10, y2+20, x2);