如何在Oracle表中为无关联数据设置层级父子关系

针对你的需求，因为层级逻辑明确且数据量不大，有两种直接的实现方式：

方法一：逐条执行UPDATE语句 #

假设你的表名为ACCOUNT_HIERARCHY，可以根据层级逻辑直接编写UPDATE语句，精准设置每个节点的PARENT_ID：

-- 设置ASSETS的父节点为ROOT
UPDATE ACCOUNT_HIERARCHY
SET PARENT_ID = (SELECT ID FROM ACCOUNT_HIERARCHY WHERE CODE = '0005')
WHERE CODE = '0015';

-- 设置CASH AND BANK BALANCE的父节点为ASSETS
UPDATE ACCOUNT_HIERARCHY
SET PARENT_ID = (SELECT ID FROM ACCOUNT_HIERARCHY WHERE CODE = '0015')
WHERE CODE = '0025';

-- 设置CASH IN HAND和BAL WITH BANGLADESH BANK的父节点为CASH AND BANK BALANCE
UPDATE ACCOUNT_HIERARCHY
SET PARENT_ID = (SELECT ID FROM ACCOUNT_HIERARCHY WHERE CODE = '0025')
WHERE CODE IN ('0035', '0120');

-- 设置CASH IN VAULT和0075的CASH AT ATM的父节点为CASH IN HAND
UPDATE ACCOUNT_HIERARCHY
SET PARENT_ID = (SELECT ID FROM ACCOUNT_HIERARCHY WHERE CODE = '0035')
WHERE CODE IN ('0045', '0075');

-- 设置CASH IN HAND LCY (VAULT)和CASH IN HAND FCY (VAULT).的父节点为CASH IN VAULT
UPDATE ACCOUNT_HIERARCHY
SET PARENT_ID = (SELECT ID FROM ACCOUNT_HIERARCHY WHERE CODE = '0045')
WHERE CODE IN ('0055', '0060');

-- 设置0085的CASH AT ATM的父节点为0075的CASH AT ATM
UPDATE ACCOUNT_HIERARCHY
SET PARENT_ID = (SELECT ID FROM ACCOUNT_HIERARCHY WHERE CODE = '0075')
WHERE CODE = '0085';

-- 设置BAL WITH BANGLADESH BANK LCY和BAL. WITH BANGLADESH FCY的父节点为BAL WITH BANGLADESH BANK
UPDATE ACCOUNT_HIERARCHY
SET PARENT_ID = (SELECT ID FROM ACCOUNT_HIERARCHY WHERE CODE = '0120')
WHERE CODE IN ('0130', '0135');

-- 提交事务
COMMIT;

方法二：使用MERGE语句批量更新 #

如果想更简洁地批量处理，可以将父子节点的CODE对应关系整理成一个临时数据集，通过MERGE语句一次性完成更新：

MERGE INTO ACCOUNT_HIERARCHY t
USING (
    SELECT 
        child_code,
        (SELECT ID FROM ACCOUNT_HIERARCHY WHERE CODE = parent_code) AS parent_id
    FROM (
        -- 按层级逻辑定义父子CODE对应关系
        SELECT '0015' AS child_code, '0005' AS parent_code FROM DUAL UNION ALL
        SELECT '0025' AS child_code, '0015' AS parent_code FROM DUAL UNION ALL
        SELECT '0035' AS child_code, '0025' AS parent_code FROM DUAL UNION ALL
        SELECT '0120' AS child_code, '0025' AS parent_code FROM DUAL UNION ALL
        SELECT '0045' AS child_code, '0035' AS parent_code FROM DUAL UNION ALL
        SELECT '0075' AS child_code, '0035' AS parent_code FROM DUAL UNION ALL
        SELECT '0055' AS child_code, '0045' AS parent_code FROM DUAL UNION ALL
        SELECT '0060' AS child_code, '0045' AS parent_code FROM DUAL UNION ALL
        SELECT '0085' AS child_code, '0075' AS parent_code FROM DUAL UNION ALL
        SELECT '0130' AS child_code, '0120' AS parent_code FROM DUAL UNION ALL
        SELECT '0135' AS child_code, '0120' AS parent_code FROM DUAL
    ) hierarchy_map
) s
ON (t.CODE = s.child_code)
WHEN MATCHED THEN
    UPDATE SET t.PARENT_ID = s.parent_id;

-- 提交事务
COMMIT;

注意事项 #

1. 请将上述SQL中的ACCOUNT_HIERARCHY替换为你实际使用的表名。
2. 执行前建议先备份数据，避免误操作。
3. 如果表中存在重复NAME的情况（如两个CASH AT ATM），必须通过CODE精准区分，这也是上述SQL使用CODE匹配的原因。

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