Skip to content
Advertisement

Query to delete record when no matching in delta table

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)

sample data

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

Advertisement

Answer

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

Result:

 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 DELETE & JOIN or using EXISTS instead of IN, but this example should be a good solution.

2 People found this is helpful
Advertisement