Deleting records based on 4 keys

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Deleting records based on 4 keys

Author

Topic

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-18 : 10:46:41

I know something like:

delete from table where key in (select key from table2)

works with little difficulty assuming the key is unique. However, I have a table that uses 4 columns to make each record unique.

My question is.. how does this work
lets take tablea and tableb.. both tables have 4 rows, a,b,c,d
tablea has the following values
1,1,1,1
1,1,1,2
1,2,1,2
2,1,1,2
2,1,1,1

tableb has in it
1,1,1,2
2,1,1,1

If I were to run the query:

delete from tableA where
a in (select a from tableB)
and b in (select b from tableB)
and c in (select c from tableB)
and d in (select d from tableB)

What would this query delete...
obviously it would delete the intended records :
1,1,1,2
2,1,1,1

but does it also delete:
2,1,1,2?

a = 2 and that is in select a from tableb
b = 1 and once again its in select b from tableb
c = 1 and it too is in select c from tableb
d = 2 and it is in select d from tableb

so sql logic, it is deleted, but obviously it's not intended. Is there brackets I'm forgetting somewhere here?

I have a feeling theres a really simple answer to this, just none of the 3 of us here can think of it.

-----------------------
Take my advice, I dare ya

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-18 : 10:57:42

You can do this in T-SQL:

DELETE FROM A
FROM TableA AS A
INNER JOIN TableB AS B
ON (A.col1=B.col1 AND A.col2=B.col2 AND A.col3=B.col3 AND A.col4=B.col4)

FYI it's not ANSI SQL, so in case Joe Celko happens to see this post don't be surprised if he screams

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-18 : 11:06:25

Sweet... Praise be to rob. I'd buy you a

if you weren't so far away.

It'd be funny if Joe Celko yells at you either way though

[edit] Hey, post number 300. Fitting that it's me asking a question [/edit]
-----------------------
Take my advice, I dare ya

Edited by - M.E. on 07/18/2002 11:07:26

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-18 : 11:21:55

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm

You can also try this, it's a little more standard SQL:

DELETE FROM TableA
WHERE EXISTS
(SELECT * FROM TableB
WHERE col1=TableA.col1 AND col2=TableA.col2 AND col3=TableA.col3 AND col4=TableA.col4)

Somehow though, I don't think it's exactly the same as the other one. You might want to test it to be sure.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources