Solved

not exists with multiple primary keys

Posted on 2007-11-29
3
215 Views
Last Modified: 2010-04-21
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,
0
Comment
Question by:Jinghui Li
3 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 20374463
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
 
LVL 22

Accepted Solution

by:
dportas earned 500 total points
ID: 20374558
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
 

Author Closing Comment

by:Jinghui Li
ID: 31411684
I tried it. It works fine.
Thank you very much,
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question