Search code examples
mysqlsqlsql-deletemysql-error-1064

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

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.

Solution

  • 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