Обновите все родительские идентификаторы в таблице SQL mptt

Question

Рассмотрим таблицу SQL, в которой хранятся иерархические данные с использованием метода MPTT (Modified Preorder Tree Traversal).

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

Теперь я хочу обновить все внешние ключи parent_id одновременно. К сожалению, мои навыки SQL действительно заржавели. Может кто-нибудь указать мне эффективный способ сделать это?

Вот так я и попробовал:

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;

К сожалению, это не похоже на работу:

ОШИБКА: подзапрос в FROM не может ссылаться на другие отношения того же уровня запроса

Спасибо за любые подсказки.

PS: это на PostgreSQL, если это имеет значение.

Answer 1

На самом деле, я нашел способ сделать это:

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;

Не уверен, что это самый эффективный способ сделать это, но он достаточно быстрый.