SQL Delete based on condition in join

sql join sql-delete
user275074 picture user275074 · Nov 4, 2010 · Viewed 45.1k times · Source

It is possible to delete records based on a satisfied condition with a join query?

For instance, I have a linking table joining 3 records. The query I have at the moment deletes records from this table where one of the id's isn't IN() an imploded Php array. I've come to realise that the query should only remove records from this table if the id's don't exist in the array and they belong to a certain other table based on the a link to another table.

Answer

Greg Dougherty picture Greg Dougherty · Apr 27, 2011

For SQL Server, the command is slightly different:

DELETE FROM TableA
FROM TableA LEFT OUTER JOIN TableB ON TableA.Column = TableB.Column 
WHERE TableB.Column IS NULL

No, that's not a typo, yes, you do need "FROM TableA" twice. At least, you need the second FROM (the first is optional). The following has the advantage that it works for both SQL Server and MySQL:

DELETE TableA
FROM TableA LEFT OUTER JOIN TableB ON TableA.Column = TableB.Column 
WHERE TableB.Column IS NULL

Related questions

What is the difference between "INNER JOIN" and "OUTER JOIN"?

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?

Read More
SQL update from one Table to another based on a ID match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers. I created a view linking the …

Read More
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Read More