Published:
Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.
I thought I would keep the SQL-101 theme going this week with a snippet I had hanging around in my helpers folder.
So the idea of this is pretty straight forward. Suppose you have a table of data where you want to delete all records from this table based on some value also existing in another table. Normally this would be some Id but it really could be anything.
DELETE FROM table1
WHERE EXISTS (
SELECT Id
FROM table2
WHERE table2.Id = table1.Id
)
Of course you can also quickly invert this by using "NOT EXISTS" to delete records where an id is not found in another table. I actually use this more often when I'm doing cleanup tasks on legacy databases.
Reader Comments