Deleting in Table1 based on a column in Table2

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

General SQL Server Forums

New to SQL Server Programming

Deleting in Table1 based on a column in Table2

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-03-10 : 14:20:20

I have two tables
(1)CustomerInfo and (2)RetainCustInfo
The above tables could be joined based on Custid column

I want to delete everything in the CustomerInfo table if there are no matches of Custid in the RetainCustInfo Table

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-10 : 14:38:41

DELETE FROM CustomerInfo ci
WHERE NOT EXISTS(select * from RetainCustInfo rci where ci.custid = rci.custid)

Jim

Obviously make this a select statement first to ensure it works!

Everyday I learn something that somebody else already knew

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-03-10 : 14:42:20

Thanks Jim
Why on Earth did i not think of Sub Queries.
I keep reading books to learn SQL but never get ideas when I need them

quote:
Originally posted by jimf

DELETE FROM CustomerInfo ci
WHERE NOT EXISTS(select * from RetainCustInfo rci where ci.custid = rci.custid)

Jim

Obviously make this a select statement first to ensure it works!

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources