I have this kind of DB2 request which work
MERGE INTO table_to_upsert AS tab USING (VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9) -- more rows ) AS merge (C1, C2, C3) ON tab.key_to_match = merge.key_to_match -- usually the primary key example : tab.C2 = merge.C2 WHEN MATCHED THEN UPDATE SET tab.C1 = merge.C1, tab.C2 = merge.C2, tab.C3 = merge.C3 WHEN NOT MATCHED THEN INSERT (C1, C2, C3) VALUES (merge.C1, merge.C2, merge.C3)
But now, what I want is, IF primary key already existe, then update, BUT only update C1 if the new value of C1 is different from the old one.
In theory something like that
MERGE INTO table_to_upsert AS tab USING (VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9) -- more rows ) AS merge (C1, C2, C3) ON tab.key_to_match = merge.key_to_match WHEN MATCHED THEN UPDATE SET tab.C1 = merge.C1, tab.C2 = merge.C2, tab.C3 = merge.C3 WHERE tab.C1 != merge.C1 WHEN NOT MATCHED THEN INSERT (C1, C2, C3) VALUES (merge.C1, merge.C2, merge.C3)
I add the WHERE tab.C1 != merge.C1
but it's not working, result in
Error: ILLEGAL SYMBOL WHERE; VALID SYMBOLS
1 Answer
If I remember correctly, you can add one search condition to the matched line, i.e.
MERGE INTO table_to_upsert AS tab USING (VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9) -- more rows ) AS merge (C1, C2, C3) ON tab.key_to_match = merge.key_to_match WHEN MATCHED AND tab.C1 != merge.C1 THEN UPDATE SET tab.C1 = merge.C1, tab.C2 = merge.C2, tab.C3 = merge.C3 WHEN NOT MATCHED THEN INSERT (C1, C2, C3) VALUES (merge.C1, merge.C2, merge.C3)
0ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoanFwaWWDen%2BOnZlrZZ2av6ixjKKlraddqr2lrdOeZKKeXauurcHEZpuinpaav6a60w%3D%3D