DB2, MERGE INTO update if value different

I have this kind of DB2 request which work 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

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) 
0

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoanFwaWWDen%2BOnZlrZZ2av6ixjKKlraddqr2lrdOeZKKeXauurcHEZpuinpaav6a60w%3D%3D

 Share!