SQL DELETE with JOIN another table on multiple conditions for WHERE NULL

Question

I have to delete rows from slaveTable that have no more relation with masterTable table (dead relations, removed from masterTable).

I came up with the statement:

DELETE
FROM   slaveTable AS s
LEFT JOIN 
       masterTable AS m 
ON   ( s.key1 = m.key1
AND    s.key2 = m.key2 )
WHERE  m.key1 IS NULL

SQL DELETE with JOIN another table for WHERE condition shows this way as a solution... but won't work for me. Because of my lack of reputation I can't answer there.

possible duplicate of [SQL DELETE with JOIN another table for WHERE condition](http://stackoverflow.com/questions/1980738/sql-delete-with-join-another-table-for-where-condition) — Brian Tompsett - 汤莱恩, Aug 27 '15 at 09:01

score 1 · Accepted Answer · answered Dec 03 '13 at 19:09

1

specify the table where you want to delete the rows.

DELETE s                    -- << HERE: s (alias of slaveTable )
FROM   slaveTable AS s
LEFT JOIN 
       masterTable AS m 
ON   ( s.key1 = m.key1
AND    s.key2 = m.key2 )
WHERE  m.key1 IS NULL

answered Dec 03 '13 at 19:09

John Woo

258,903
69
498
492

SQL DELETE with JOIN another table on multiple conditions for WHERE NULL

1 Answers1