Learn how to delete rows with MySQL LEFT JOIN

By admin|MySQL

Think you have 2 tables with foreign key. Then, you can select some records using LEFT JOIN.

SELECT * FROM talbe_1 LEFT JOIN talbe_2 ON talbe_1.fk_id = talbe_2.fk_id WHERE talbe_1.status = 1;

But, if you need to delete using LEFT JOIN then you get a sql error.

DELETE * FROM talbe_1 LEFT JOIN talbe_2 ON talbe_1.fk_id = talbe_2.fk_id WHERE talbe_1.status = 1;

MySQL error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'talbe_2 ON talbe_1.fk_id = talbe_2.fk_id WHERE talbe_1.status = 1 at line 1

Solution for this issue.
Delete only the talbe_1 rows:

DELETE talbe_1 FROM talbe_1 LEFT JOIN talbe_2 ....

Delete both tables rows:

DELETE talbe_1, talbe_2 FROM talbe_1 LEFT JOIN talbe_2 ....