DELETE with INTERSECT in DB2

Question

Is it possible to delete all records from table A which exist in table B if there are no primary or foreign keys? Similar to this answer:

DELETE a 
FROM @A a
WHERE EXISTS (SELECT a.* INTERSECT SELECT * FROM @B)

In DB2 each SELECT has to have a FROM clause and FROM sysibm.sysdummy1 does not work here.

Not really my table. There is a key column, however it is not unique. — Peter, Sep 13 '17 at 18:11

score 1 · Answer 1 · answered Sep 13 '17 at 13:47

Personally, I'd probably just use a multi-step process...

create table tmp as (
select * from @A
EXCEPT
select * from @B
);

delete from @A;

insert into @A
 select * from tmp;

Anything else I can think of seems to require an explicit list of column names.

delete 
from @A a
where exists (select *
              from @B b
              where a.Fld1 = b.Fld1
                   and a.Fld2 = b.Fld2
                  <...>
             );

Also considered a quantified predicate and the IN predicate...something like

delete 
from @A a
where (a.*) in (select * from @B);

delete 
from @A a
where (a.*) <> ALL (select * from @B);

But I don't believe (a.*) is considered a row-value-expression and regardless the documentation for both say that

SELECT * is not allowed in the outermost select lists of the fullselect

Lastly, note that all of these are going to have problems if there are any NULL columns.

score 0 · Answer 2 · answered Sep 13 '17 at 14:29

try somthing like this :

delete from @A a
where exists 
(
 select * from @b b
 where 
 (b.field1=a.field1 or b.field1 is null and a.field1 is null) and  
 (b.field2=a.field2 or b.field2 is null and a.field2 is null) and  
 (b.field3=a.field3 or b.field3 is null and a.field3 is null)   
)

DELETE with INTERSECT in DB2

2 Answers2