为层级表v_name所有子节点添加并填充root_id字段求助

首先确认已新增root_id字段（若未添加，执行以下语句）：

ALTER TABLE v_name ADD root_id NUMBER;

你提供的脚本存在多处逻辑错误，导致执行失败，以下是正确的填充方案：

方案1：使用UPDATE结合分层查询 #

UPDATE v_name t
SET t.root_id = (
    -- 从当前节点向上追溯，获取顶级父ID
    SELECT CONNECT_BY_ROOT id
    FROM v_name
    START WITH id = t.id
    CONNECT BY PRIOR child_id = id
);

方案2：使用MERGE批量更新（更高效） #

MERGE INTO v_name t
USING (
    -- 遍历所有层级节点，生成每个节点对应的顶级父ID
    SELECT id, CONNECT_BY_ROOT id AS root_id
    FROM v_name
    START WITH child_id IS NULL -- 从顶级节点开始遍历
    CONNECT BY child_id = PRIOR id -- 子节点的child_id关联父节点的id
) s
ON (t.id = s.id)
WHEN MATCHED THEN
    UPDATE SET t.root_id = s.root_id;

原脚本错误说明 #

• 错误使用INSERT语句：你的需求是更新现有记录的root_id，而非插入新行，应使用UPDATE或MERGE
• START WITH条件错误：顶级节点的判断是child_id IS NULL，而非id = null
• CONNECT BY关联逻辑颠倒：正确的层级遍历逻辑应为child_id = PRIOR id，确保从父节点向下遍历所有子节点
• 未获取正确的顶级父ID：需要用CONNECT_BY_ROOT id来获取当前节点的顶级父ID，原脚本选择id和LEVEL不符合需求

内容的提问来源于stack exchange，提问作者Rajib Sharma