Update all parent id's in a SQL mptt table

Question

Hi,

consider a SQL table, which stores hierarchical data using MPTT (Modified Preorder Tree Traversal) method.

CREATE TABLE node (
    id              SERIAL          NOT NULL, -- primary key

    -- Nested mptt tree model.
    lft             INT             NOT NULL,
    rgt             INT             NOT NULL,

    -- Some legacy applications still need to access parent nodes using a foreign key
    parent_id       INT,

    FOREIGN KEY(parent_id) REFERENCES node
);

Now I'd like to update all the parent_id foreign keys at once. Unfortunately, my SQL skills are really rusted. Can anyone point me to an efficient way to do it?

That's the way I tried it:

UPDATE node AS main 
SET main.parent_id=ss.id 
FROM (
    SELECT parent.id 
    FROM node AS parent 
    WHERE main.lft BETWEEN parent.lft AND parent.rgt
    ORDER BY parent.lft DESC) ss;

Unfortunately, this does not seem to be working:

ERROR: subquery in FROM cannot refer to other relations of same query level

Thanks for any hints.

PS: This is on PostgreSQL, if it matters.

Answer 1

Actually, I found a way to do it:

UPDATE node 
SET parent_id=ss.id 
FROM (
    SELECT id, lft, rgt 
    FROM node ORDER BY lft DESC) ss 
WHERE ss.lft < node.lft AND ss.rgt > node.rgt;

Not sure if its the most efficient way to do it but it's fast enough.