仅存直接父子关联时如何更新父设施及全层级子设施value字段

你当前的查询仅做了一次caip_facility父-子关联，只能覆盖第一层直接子节点，对于无固定深度的树形结构，必须通过递归遍历的方式拉取全量子节点，无法通过固定层数的join实现全覆盖。

使用**递归CTE（递归公共表表达式）**实现全层级节点遍历，这是所有主流关系型数据库针对树形结构查询的标准方案，不受层级深度限制，整体流程分3步：

• 锚定你要更新的顶层目标设施作为遍历起点
• 递归关联设施表，通过parentid = 上一层节点的facilityid的规则逐层下钻，直到没有新的子节点被找到，拿到整棵子树的所有设施ID
• 关联属性映射表和属性定义表，筛选出需要更新的AUTO_CREATE_PROCESS类型属性，执行批量更新

-- 注意：MySQL 8.0+/PostgreSQL/SQL Server/Oracle 11gR2+ 均支持递归CTE语法
WITH RECURSIVE all_facility_tree AS (
    -- 锚点：替换成你实际要更新的顶层设施ID
    SELECT facilityid
    FROM caip_facility
    WHERE facilityid = '替换为你的目标顶层父设施ID'

    UNION ALL

    -- 递归逻辑：逐层拉取下一级子节点
    SELECT f.facilityid
    FROM caip_facility f
    INNER JOIN all_facility_tree aft ON f.parentid = aft.facilityid
    -- 若担心数据存在循环引用（如子节点parentid指向祖先节点形成环），可加路径校验避免死循环
    -- 示例（PostgreSQL）：WHERE f.facilityid <> ALL(aft.traverse_path)，对应锚点要加traverse_path字段存遍历过的ID数组
),
-- 关联筛选出所有需要更新的属性记录
target_update_records AS (
    SELECT fa.id
    FROM all_facility_tree aft
    JOIN caip_facility_attribute fa ON fa.facilityid = aft.facilityid
    JOIN caip_attribute a ON a.id = fa.attributeid
    WHERE a.definitionTypeKey = 'AUTO_CREATE_PROCESS'
)
-- 执行批量更新，以下为PostgreSQL/MySQL通用写法
UPDATE caip_facility_attribute
SET value = true
WHERE id IN (SELECT id FROM target_update_records);

-- SQL Server用户可使用CTE直接关联更新的写法：
-- UPDATE fa
-- SET fa.value = 1
-- FROM caip_facility_attribute fa
-- INNER JOIN target_update_records tur ON fa.id = tur.id;

• 执行更新前，先把最后的UPDATE语句改成SELECT COUNT(*) FROM target_update_records或者直接查关联的facilityid列表，确认覆盖的节点范围符合预期，避免误操作。
• 若你的数据库是MySQL 5.x及以下不支持递归CTE的版本，可写存储过程实现相同逻辑：创建临时表存储已遍历的节点ID，循环插入当前层的子节点ID，直到循环插入的记录数为0即遍历完成，再基于临时表的ID做更新即可。
• 若单棵设施子树的节点量级较大（万级以上），建议给caip_facility表的parentid字段加普通索引，可大幅提升递归遍历的查询效率。

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