I'm trying to run an update query on this table to change any reference of 'YYY' to 'XXX' for COL1 while ignoring any rows that have already been updated this way. COL1 AND COL2 are primary keys. Is there a difference between running a merge into query vs an update query with a not exists in the where clause.
Using DB2 I have tried the two queries and I want to know if their results would be different:
UPDATE TABLE_NAME T1
SET COL1 = 'XXX'
WHERE COL1 = 'YYY'
AND NOT EXISTS (SELECT COL1 FROM TABLE_NAME T2 WHERE COL1 = 'XXX' and T2.COL2 = T1.COL2)
vs
MERGE INTO TABLE_NAME T1
USING (SELECT COL1, COL2 FROM TABLE_NAME IT1 WHERE COL1 = 'YYY'
AND NOT EXISTS (SELECT COL1, COL2 FROM TABLE_NAME IT2
WHERE COL1= 'XXX'
AND IT2.COL2= IT1.COL2)) T2
ON T1.COL1 = T2.COL1 AND T1.COL2 = T2.COL2
WHEN MATCHED THEN
UPDATE SET COL1 = 'XXX'