如何处理嵌套层级，正确更新数据表中的主父ID

你的需求是给表中所有行的Master Parent ID列填充最顶层的主父ID（即6），但当前的UPDATE语句只能更新到直接父节点的父ID，无法递归找到最顶层节点。

问题重现 #

目标结果数据：

Child ID     Parent ID   Master Parent ID
1             2           6
2             3           6
3             4           6
4             5           6
5             6           6
6             6           6

你尝试执行的UPDATE语句：

UPDATE table dummy t1
SET t1.Master_Parent_ID = t2.Parent_ID
FROM table dummy t2    
WHERE t1.Parent_ID = t2.Child_id

执行后得到的错误结果：

Child ID     Parent ID   Master Parent ID
1             2           3
2             3           4
3             4           5
4             5           6
5             6           6
6             6           6

解决方案 #

要递归找到每个节点的最顶层父ID，需要使用**递归CTE（公共表表达式）**遍历树形结构，再用CTE的结果更新原表。

通用关系型数据库写法（适配PostgreSQL、SQL Server等） #

-- 用递归CTE获取每个Child ID对应的顶层父ID
WITH RECURSIVE Hierarchy AS (
    -- 先定位顶层节点：Parent ID和自身Child ID相同的行（即ID=6的节点）
    SELECT 
        Child_ID, 
        Parent_ID, 
        Parent_ID AS Master_Parent_ID
    FROM dummy
    WHERE Parent_ID = Child_ID
    UNION ALL
    -- 递归遍历所有子节点，把顶层父ID逐层传递下去
    SELECT 
        d.Child_ID, 
        d.Parent_ID, 
        h.Master_Parent_ID
    FROM dummy d
    JOIN Hierarchy h ON d.Parent_ID = h.Child_ID
    WHERE d.Parent_ID != d.Child_ID
)
-- 用CTE的结果更新原表
UPDATE dummy t1
SET Master_Parent_ID = t2.Master_Parent_ID
FROM Hierarchy t2
WHERE t1.Child_ID = t2.Child_ID;

MySQL 8.0+ 专属写法 #

如果使用MySQL 8.0及以上版本，更新语句格式略有不同：

WITH RECURSIVE Hierarchy AS (
    SELECT 
        Child_ID, 
        Parent_ID, 
        Parent_ID AS Master_Parent_ID
    FROM dummy
    WHERE Parent_ID = Child_ID
    UNION ALL
    SELECT 
        d.Child_ID, 
        d.Parent_ID, 
        h.Master_Parent_ID
    FROM dummy d
    JOIN Hierarchy h ON d.Parent_ID = h.Child_ID
    WHERE d.Parent_ID != d.Child_ID
)
UPDATE dummy t1
JOIN Hierarchy t2 ON t1.Child_ID = t2.Child_ID
SET t1.Master_Parent_ID = t2.Master_Parent_ID;

原理说明 #

1. 递归CTE先锁定顶层节点（Parent ID = Child ID的行），它的顶层父ID就是自身。
2. 然后通过递归关联，把顶层父ID传递给所有下层子节点，让每个节点都能拿到最顶层的主父ID。
3. 最后用CTE生成的正确映射关系，批量更新原表的Master Parent ID列。

内容的提问来源于stack exchange，提问作者Kundu Akash