6

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?

Jacob Cohen
  • 1,232
  • 3
  • 13
  • 33
  • See this post: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – Vijay Verma Oct 26 '13 at 11:14

1 Answers1

16

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

DELETE FROM Menus
WHERE restid NOT IN (SELECT id FROM Eng_Restaurants)
Guillaume Poussel
  • 9,572
  • 2
  • 33
  • 42
  • 4
    I just facepalmed myself so hard! Thanks. – Jacob Cohen Oct 26 '13 at 11:21
  • This might not be efficient if Eng_Restaurants has lots of rows? Best solution in that case is probably to first query all IDs to be deleted then do `where restid in (...` – mozey Nov 14 '19 at 14:30