Solved

not exists with multiple primary keys

Posted on 2007-11-29
3
206 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:ScottPletcher
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now