Menu
I have recently started using relations between tables, and when I tried to connect 2 I found that there are some IDs that have nonexistent foreign keys.
For this issue lets assume I have restaurants table with id and name, and menus table with id rest_id that is a foreign key from the restaurant table.
I wanna delete all the menus that has rest_id that doesn't exist in restaurants table.
I used this:
DELETE FROM `Menus` WHERE restid IN (SELECT DISTINCT `restid` from
`Menus` M left join `Eng_Restaurants` ER on M.`restid` = ER.`ID`
where ER.`ID` is null)
But I got the following error: #1093 - You can't specify target table 'Menus' for update in FROM clause.
Note that the subquery does return the ids that doesn't exist in restaurants table:
(SELECT DISTINCT `restid` from
`Menus` M left join `Eng_Restaurants` ER on M.`restid` = ER.`ID`
where ER.`ID` is null)
Any ideas?
588
Deleting rows based on another table. Sometimes we need to delete rows based on another table. This table might exist in the same database or not. We can use the table lookup method or SQL join to delete these rows.
Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!
You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted.
You do not need to make a JOIN in your subquery:
DELETE FROM Menus
WHERE restid NOT IN (SELECT id FROM Eng_Restaurants)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
