CODEWITHSUNDEEP
mysqlsql
qwerty
qwerty

Reputation: 107

DELETE FROM 2 tables with JOIN (SQL)

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.

Upvotes: 1

Views: 77

Answers (3)

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

You may try like this using the LEFT JOIN:

DELETE `table1`, `table2` 
FROM `table1` LEFT JOIN `table2` ON table2.col = table1.id WHERE table1.id = 1

Upvotes: 2

Yanire Romero
Yanire Romero

Reputation: 482

You should use a LEFT JOIN in other to achieve this:

DELETE `table1`, `table2` 
FROM `table1` 
LEFT JOIN `table2` 
ON table2.col = table1.id WHERE table1.id = 1

Take a look here for further documentation:

http://www.w3schools.com/sql/sql_join_left.asp

Hope it helps.

Upvotes: 3

Barmar
Barmar

Reputation: 780714

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.

Upvotes: 3

Related Questions