CODEWITHSUNDEEP
mysqlsql
Jacob Cohen
Jacob Cohen

Reputation: 1272

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

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?

Upvotes: 6

Views: 5295

Answers (1)

Guillaume Poussel
Guillaume Poussel

Reputation: 9822

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

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

Upvotes: 16

Related Questions