Delete using multiple tables - SQL Server Forums

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

Transact-SQL (2000)

Delete using multiple tables

Author

Topic

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-08-08 : 07:45:33

Hi, I have a table which I want to delete records from. I want to delete these records based on the following criteria:

Delete from tablea where tablea.field1 = tableb.field1
and tablea.field2 = tableb.field2
and tablea.field3 = tableb.field3

How do I do this in sql??

Any info will be greatly appreciated

dsdeming

479 Posts

Posted - 2003-08-08 : 07:52:54

DELETE FROM tablea
FROM tablea
JOIN tableb b ON tablea.field1 = b.field1
AND tablea.field2 = b.field2
AND tablea.field3 = b.field3

Maybe the join supplies all the filtering you need, but delete statements without WHERE clauses make me nervous.

Dennis

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-08-08 : 07:56:07

Delete tablea
from tablea
INNER JOIN tableb
ON
tablea.field1 = tableb.field1
and tablea.field2 = tableb.field2
and tablea.field3 = tableb.field3

Enjoy working

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-08 : 07:56:50

Hi Paul!!

Delete tablea from
tablea inner join tableb
on
tablea.field1 = tableb.field1
and tablea.field2 = tableb.field2
and tablea.field3 = tableb.field3

- Vit

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-08 : 07:58:51

....or,


DELETE  tablea
FROM tablea A
WHERE EXISTS (SELECT tableb.* 
			FROM tableb WHERE tableb.field1 = A.field1
                                    AND tableb.field2 = A.field2
                                    AND tableb.field3 = A.field3)

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-08 : 08:49:09

quote:
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

Sounds as if you warn:

Hey, MS guys, don't even dream about getting me into your team.

PS Sorry Amethystium :) All the day a man near to me play on his recorder
old songs of C.C. Catch. So I'm in somewhat frolic and nostalgiac mood.

- Vit

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-08 : 09:14:01

quote:

All the day a man near to me play on his recorder
old songs of C.C. Catch.

I'm glad I'm not in your shoes rightnow Vit!

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-08 : 09:18:38

Why? I like her songs.

- Vit

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-08 : 09:19:57

quote:
Originally posted by Stoad

Why? I like her songs.

- Vit

I don't know... I would rather listen to Dead can Dance to be honest.

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-08 : 09:22:28

Oh... I've understood the difference... I don't
understand that what she sings about. :)

- Vit

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-08-08 : 10:50:34

Thanks for the replies guys!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources