Search code examples
mysqlnested-sets

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
);

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?

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