Delete from a table where a matching value exists in another

Author: Steven Neiland
Published:

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

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain advertisments or appear to be created for the purpose of link building, will not be published.

Archives Blog Listing