如何编写SQL更新语句遍历identicalFlag链，统一分组根标识？

核心思路 #

我们需要递归遍历每个节点的identicalFlag链，找到其最终的根节点（即id = identicalFlag的节点），然后将当前节点的identicalFlag直接更新为该根节点的ID，不管是单元素分组还是链式分组都能覆盖。

不同数据库的实现方案 #

1. MySQL 8.0+（支持递归CTE） #

先用递归CTE查出每个节点对应的根节点ID，再执行更新：

WITH RECURSIVE cte AS (
    -- 初始：直接定位所有根节点（自身ID等于identicalFlag的行）
    SELECT id, identicalFlag AS root_id
    FROM your_table
    WHERE id = identicalFlag
    
    UNION ALL
    
    -- 递归：遍历所有子节点，继承父节点的根ID
    SELECT t.id, c.root_id
    FROM your_table t
    JOIN cte c ON t.identicalFlag = c.id
    WHERE t.id != t.identicalFlag
)
UPDATE your_table t
JOIN cte c ON t.id = c.id
SET t.identicalFlag = c.root_id;

2. PostgreSQL #

PostgreSQL的递归CTE更新逻辑类似：

WITH RECURSIVE cte AS (
    SELECT id, identicalFlag AS root_id
    FROM your_table
    WHERE id = identicalFlag
    
    UNION ALL
    
    SELECT t.id, c.root_id
    FROM your_table t
    JOIN cte c ON t.identicalFlag = c.id
    WHERE t.id != t.identicalFlag
)
UPDATE your_table t
SET identicalFlag = c.root_id
FROM cte c
WHERE t.id = c.id;

3. SQL Server #

SQL Server的递归CTE更新写法如下：

WITH RECURSIVE cte AS (
    SELECT id, identicalFlag AS root_id
    FROM your_table
    WHERE id = identicalFlag
    
    UNION ALL
    
    SELECT t.id, c.root_id
    FROM your_table t
    INNER JOIN cte c ON t.identicalFlag = c.id
    WHERE t.id != t.identicalFlag
)
UPDATE t
SET t.identicalFlag = c.root_id
FROM your_table t
INNER JOIN cte c ON t.id = c.id;

4. 不支持递归CTE的老版本数据库（如MySQL 5.x） #

可以用循环迭代的方式，逐层将节点的identicalFlag向上替换，直到所有节点都指向根：

WHILE EXISTS (
    SELECT 1 FROM your_table 
    WHERE identicalFlag != (
        SELECT identicalFlag FROM your_table t2 
        WHERE t2.id = your_table.identicalFlag
    )
) DO
    UPDATE your_table t1
    SET t1.identicalFlag = (
        SELECT t2.identicalFlag FROM your_table t2 
        WHERE t2.id = t1.identicalFlag
    )
    WHERE t1.identicalFlag != t1.id;
END WHILE;

注意事项 #

• 把语句中的your_table替换成你实际使用的表名。
• 给identicalFlag列加索引，避免递归或循环更新时性能拖慢。
• 执行前建议先备份数据，或者在测试环境验证逻辑没问题再跑生产。

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