CODEWITHSUNDEEP
mysqlnested-sets
mingos
mingos

Reputation: 24532

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?

Upvotes: 1

Views: 699

Answers (2)

Quassnoi
Quassnoi

Reputation: 425753

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

Upvotes: 1

Derek
Derek

Reputation: 23308

Give this a try:

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

Upvotes: 0

Related Questions