MySQL update an item of a nested set

I have a table in my database which conains a nested set of items, with significant columns being id and parent. parent always points to the row id it is nested within.

When I delete an item, its direct children start pointing to a nonexistent parent, which is something I'd like to correct.

I have tried this query in order to reset the parent of any orphaned items:

UPDATE menu_item
SET parent = 0
WHERE parent NOT IN (
    SELECT id FROM menu_item开发者_如何学Python
);

However, it gives me an error: "You can't specify target table 'menu_item' for update in FROM clause."

What is the correct way to tackle this?

---

UPDATE  mytable m
LEFT JOIN
        mytable mm
ON      mm.id = m.parent
SET     m.parent = 0
WHERE   mm.id IS NULL

---

Give this a try:

update m
set parent = 0
from menu_item m
where m.parent not in (select id from menu_item m1);