Oracle MERGE更新多记录时，如何基于已更新记录取MAX值？

你碰到的这个问题其实是Oracle MERGE的一个常见特性导致的：MERGE在执行UPDATE时，所有子查询都会基于更新前的表数据快照来计算，而不是实时读取已经被更新的行。所以当你批量把parent='A'的记录改成'B'时，每条记录执行SELECT NVL(MAX(occurrence)) +1的时候，看到的还是原表中parent='B'的旧数据（也就是B/M的最大值是1），结果就是所有A/M的记录都被设置成了occurrence=2，而不是你期望的2和3。

最优解决方案：在USING子句中预计算目标值 #

要解决这个问题，我们需要提前把所有要合并到B下的记录（包括原有的B记录和要更新的A记录）整合起来，用窗口函数计算好每条记录的目标occurrence值，这样就不用依赖实时的表数据了。

直接上可用的SQL：

MERGE INTO STRUCTURE a
USING (
    -- 整合原有B的记录和待更新的A的记录，计算每个child对应的新occurrence
    SELECT
        src.parent,
        src.child,
        src.occurrence AS original_occurrence,
        'B' AS new_parent,
        -- 按child分组排序，生成行号后加上B的基础最大值，得到新的occurrence
        ROW_NUMBER() OVER (PARTITION BY src.child ORDER BY src.occurrence) + COALESCE(base.base_max, 0) AS new_occurrence
    FROM (
        -- 取出所有B的现有记录 + 所有A的待更新记录
        SELECT parent, child, occurrence FROM STRUCTURE WHERE parent = 'B'
        UNION ALL
        SELECT parent, child, occurrence FROM STRUCTURE WHERE parent = 'A'
    ) src
    -- 关联每个child在B中的当前最大occurrence值
    LEFT JOIN (
        SELECT child, MAX(occurrence) AS base_max 
        FROM STRUCTURE 
        WHERE parent = 'B' 
        GROUP BY child
    ) base ON src.child = base.child
    -- 只保留需要更新的A的记录，B的记录不用处理
    WHERE src.parent = 'A'
) b
-- 通过parent+child+original_occurrence精准匹配要更新的行
ON (a.parent = b.parent AND a.child = b.child AND a.occurrence = b.original_occurrence)
WHEN MATCHED THEN
UPDATE SET 
    parent = b.new_parent,
    occurrence = b.new_occurrence;

逻辑拆解 #

1. 数据整合：先把原表中parent='B'的记录和要更新的parent='A'的记录合并，这样我们能看到每个child下所有即将归属B的完整数据集。
2. 基础最大值计算：提前算出每个child在B中的当前最大occurrence，比如M的base_max是1，F的base_max是0（因为原表中没有B/F的记录）。
3. 生成递增的occurrence：对每个child分组，按原occurrence排序，用ROW_NUMBER()生成连续的序号，加上base_max就得到了新的occurrence。比如A/M的两条记录，ROW_NUMBER是1和2，加上1就得到2和3；A/F的ROW_NUMBER是1，加上0得到1。
4. 精准更新：通过parent+child+original_occurrence的组合条件，确保每条待更新的A记录都能被准确匹配并设置新值。

执行后，你的表数据会完全符合预期：

替代方案：用UPDATE语句实现 #

如果不想用MERGE，也可以直接用UPDATE语句，逻辑和上面一致：

UPDATE STRUCTURE a
SET (parent, occurrence) = (
    SELECT 
        'B',
        ROW_NUMBER() OVER (PARTITION BY a.child ORDER BY a.occurrence) + COALESCE(base.base_max, 0)
    FROM (
        SELECT child, MAX(occurrence) AS base_max 
        FROM STRUCTURE 
        WHERE parent = 'B' 
        GROUP BY child
    ) base
    WHERE base.child = a.child
)
WHERE a.parent = 'A';

这个语句直接针对parent='A'的行进行更新，同样通过窗口函数计算出正确的occurrence值。

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