title: MySQL update an item of a nested set
body: <p>I have a table in my database which conains a nested set of items, with significant columns being <code>id</code> and <code>parent</code>. <code>parent</code> always points to the row id it is nested within.</p>
  
  <p>When I delete an item, its direct children start pointing to a nonexistent parent, which is something I'd like to correct.</p>
  
  <p>I have tried this query in order to reset the parent of any orphaned items:</p>
  
  <pre><code>UPDATE menu_item
  SET parent = 0
  WHERE parent NOT IN (
      SELECT id FROM menu_item
  );
  </code></pre>
  
  <p>However, it gives me an error: "You can't specify target table 'menu_item' for update in FROM clause."</p>
  
  <p>What is the correct way to tackle this?</p>