DELETE values from one table with id that doesn't exist in another in mysql

Question

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?

Answer 1

You do not need to make a JOIN in your subquery:

DELETE FROM Menus
WHERE restid NOT IN (SELECT id FROM Eng_Restaurants)