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.

What Do You Think?

Reader Comments

Comment Etiquette:

  • 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 or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.
*
*



Archives Blog Listing