You cannot update primary index or partitioning column unless the source is a valid single-row subquery.
Suppose you have created the following tables:
CREATE TABLE t1 ( x1 INTEGER, y1 INTEGER, z1 INTEGER) PRIMARY INDEX(x1); CREATE TABLE t2 ( x2 INTEGER, y2 INTEGER, z2 INTEGER) PRIMARY INDEX(x2) UNIQUE INDEX(y2);
The following case is valid:
MERGE INTO t1 USING (SELECT x2, y2, z2 FROM t2 WHERE y2=10) AS s ON x1=10 AND y1=20 WHEN MATCHED THEN UPDATE SET x1=10 WHEN NOT MATCHED THEN INSERT (y2, z2, x2);
The following case is not valid because while the subquery guarantees a single row, no constant in specified in the ON clause equality condition, so you cannot update the primary index as this request attempts to do:
MERGE INTO t1 USING (SELECT x2,y2, z2 FROM t2 WHERE y2=10) AS s ON x1=y2 AND y1=z2 WHEN MATCHED THEN UPDATE SET x1=10 WHEN NOT MATCHED THEN INSERT (y2, z2, x2);