Oracle单表层级批量更新查询问题求助

看起来你是要给每个关联链中的非根节点，把beginner_related_person_id更新为对应链的根节点person_id对吧？完全不需要写循环，Oracle自带的递归查询能力就能完美解决这个问题，而且效率比循环高得多。

先分析你的现有语句问题 #

你当前的更新语句有几个小问题：

• 依赖的beginner_person_id应该是笔误（应该是beginner_related_person_id），但这个字段目前全是null，根本没法用来做递归关联；
• START WITH的条件逻辑不对，应该从没有上级的根节点开始递归，而不是所有有上级的节点。

正确的实现方案 #

我们可以用Oracle的CONNECT_BY_ROOT函数，它能直接获取递归树中每个节点的根节点ID，结合CONNECT BY语法就能快速拿到所有节点对应的根节点，再用这个结果更新原表。

第一步：先验证递归查询的结果

先执行下面的查询，确认每个person_id对应的根节点是否正确：

SELECT 
    person_id,
    related_person_id,
    CONNECT_BY_ROOT person_id AS root_id
FROM person
START WITH related_person_id IS NULL
CONNECT BY PRIOR person_id = related_person_id
ORDER BY person_id;

这个语句的逻辑是：

• START WITH related_person_id IS NULL：找到所有没有上级的根节点（比如你的示例中的1、4、6）；
• CONNECT BY PRIOR person_id = related_person_id：递归关联子节点（子节点的related_person_id等于父节点的person_id）；
• CONNECT_BY_ROOT person_id：直接获取当前节点所在递归树的根节点ID。

第二步：用查询结果更新原表

你可以用UPDATE语句结合子查询来更新：

UPDATE person p
SET beginner_related_person_id = (
    SELECT root_id
    FROM (
        SELECT 
            person_id,
            CONNECT_BY_ROOT person_id AS root_id
        FROM person
        START WITH related_person_id IS NULL
        CONNECT BY PRIOR person_id = related_person_id
    ) sub
    WHERE sub.person_id = p.person_id
)
WHERE p.related_person_id IS NOT NULL; -- 只更新非根节点，根节点保持null

或者用MERGE语句，对于大数据量的场景，MERGE的性能通常更优：

MERGE INTO person p
USING (
    SELECT 
        person_id,
        CONNECT_BY_ROOT person_id AS root_id
    FROM person
    START WITH related_person_id IS NULL
    CONNECT BY PRIOR person_id = related_person_id
) sub
ON (p.person_id = sub.person_id)
WHEN MATCHED AND p.related_person_id IS NOT NULL THEN
    UPDATE SET p.beginner_related_person_id = sub.root_id;

为什么不用循环？ #

用PL/SQL写循环逐行更新的话，不仅代码繁琐，而且对于大数据量来说，逐行操作的效率远低于Oracle基于集合的SQL优化。上面的方案是一次性处理所有数据，数据库会自动优化执行计划，性能和维护性都更好。

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