please help to write a better delete query. DB2 does not have “not matched by source” with merge. Need to delete rows which does not have matching codes from source file for an ID and need to delete rows only for the ID’s present in source file.
delete from target where ID in ( select ID from source) and ID concat Code NOT in ( Select S.ID concat S.Code from source S)
target source ID code ID code 1 ABC 1 ABC 1 DEF 1 DEF 1 IJK 2 ABC 2 XYZ
row with value ID=1 & code=IJK alone need to be deleted from target
You can do:
delete from target where (id, code) in ( select t.id, t.code from target t left join source s on (s.id, s.code) = (t.id, t.code) where s.id is null and t.id in (select id from source) );
ID CODE --- ---- 1 ABC 1 DEF 2 ABC 2 XYZ
See running example at db<>fiddle.
I’m sure the syntax can be compressed using
JOIN or using
EXISTS instead of
IN, but this example should be a good solution.