title: DELETE values from one table with id that doesn't exist in another in mysql
body: <p>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.</p>
  
  <p>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.</p>
  
  <p><strong>I wanna delete all the menus that has rest_id that doesn't exist in restaurants table.</strong></p>
  
  <p>I used this:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>But I got the following error: #1093 - You can't specify target table 'Menus' for update in FROM clause.</p>
  
  <p>Note that the subquery does return the ids that doesn't exist in restaurants table:</p>
  
  <pre><code>(SELECT DISTINCT `restid` from 
         `Menus` M left join `Eng_Restaurants` ER on M.`restid` = ER.`ID`
      where ER.`ID` is null)
  </code></pre>
  
  <p>Any ideas?</p>