DELETE `table1`, `table2` FROM `table1` JOIN `table2` ON table2.col = table1.id WHERE table1.id = 1
If in table2 no result with table2.col = table1.id then it sql query doesn't work. If table2 has row with col = table.id then it works perfectly. How to remake it? I need to delete row fom table1 even if in table2 row with table.col = table1.id doesn't exists.
Thanks in advance.
Use a LEFT JOIN.
DELETE `table1`, `table2`
FROM `table1`
LEFT JOIN `table2` ON table2.col = table1.id
WHERE table1.id = 1
The general rule is that a DELETE query will delete the same rows that would be returned if you did a SELECT query with the same parameters. Since you would use a LEFT JOIN in a SELECT to get rows from table1 that have no match in table2, you have to do the same thing with DELETE.
This general rule is also helpful if you want to test a DELETE safely. Perform the corresponding SELECT, and make sure it returns only the rows you want to delete.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments