not exists with multiple primary keys

Hi,
  I want to write a store procedure to delete the records in Table A not in Table B. Table A and Table B have the same primary keys. Since both of them use multiple primary keys, the following code doesn't work correctly.

delete from A
      where not exists
      (select
            B.*
       from B
       inner join A
       on
            A.k1= B.k1 and A.k2= B.k2 and
            A.k3= B.k3 and A.k4= B.k4 and A.k5= B.k5 and A.k6 = B.k6
      )

Many thanks,
Jinghui LiAsked:
Who is Participating?
 
dportasConnect With a Mentor Commented:
You were very close I think, but the extra reference to A was the problem (your subquery wasn't correlated because of it).

Untested. Make sure you test this out and have a backup and before you try it for real!

delete from A
      where not exists
      (select
            B.*
       from B
       where
            A.k1= B.k1 and A.k2= B.k2 and
            A.k3= B.k3 and A.k4= B.k4 and A.k5= B.k5 and A.k6 = B.k6
      );
0
 
Scott PletcherSenior DBACommented:
DELETE FROM A
FROM A
LEFT OUTER JOIN B ON A.k1= B.k1 and A.k2= B.k2 and
            A.k3= B.k3 and A.k4= B.k4 and A.k5= B.k5 and A.k6 = B.k6
WHERE B.k1 IS NULL
0
 
Jinghui LiAuthor Commented:
I tried it. It works fine.
Thank you very much,
0
All Courses

From novice to tech pro — start learning today.