基于条件更新PRICELIST表LOOPSETID字段的SQL/MERGE语句问题

原MERGE语句存在两处核心问题：

• 关联逻辑失效：ON子句仅筛选ps.VAL_STATUS='Y'，未建立目标表pt与源表ps的行级唯一关联，会导致所有符合条件的行被重复匹配，甚至错误更新无关记录。
• SET子句语法错误：直接使用未封装的SELECT子句赋值，且子查询未与当前更新行绑定，会返回多行结果，既触发ORA-00936语法错误，也无法完成单行赋值。

假设BU_NAME是表的唯一标识（用于精准关联每行数据），我们可以在USING子句中先计算出符合条件的行对应的LOOPSETID，再通过唯一键关联目标表完成更新：

MERGE INTO PRICELIST_SRC_TBL pt
USING (
    SELECT 
        BU_NAME,
        TRUNC((ROW_NUMBER() OVER(ORDER BY BU_NAME) + 2 - 1) / 2) AS LOOPSETID
    FROM PRICELIST_SRC_TBL
    WHERE VAL_STATUS = 'Y'
) ps
ON (pt.BU_NAME = ps.BU_NAME)
WHEN MATCHED THEN
    UPDATE SET pt.LOOPSETID = ps.LOOPSETID;

1. USING子查询：
   • 仅筛选VAL_STATUS='Y'的记录，用ROW_NUMBER()生成行号（按BU_NAME排序保证分组顺序稳定，若无需特定顺序可替换为其他字段或移除ORDER BY，但建议保留以确保结果一致性）。
   • 套用给定公式TRUNC((rowno + p_max_batch_rec -1)/p_max_batch_rec)计算LOOPSETID（此处p_max_batch_rec=2）。
2. 关联条件：通过BU_NAME将目标表与子查询结果绑定，确保每行仅被匹配一次，避免重复更新。
3. 更新逻辑：仅匹配到VAL_STATUS='Y'的行才会被更新，VAL_STATUS='N'的行保持LOOPSETID=0不变。

若无需使用MERGE，也可通过带窗口函数的UPDATE语句实现需求：

UPDATE PRICELIST_SRC_TBL pt
SET LOOPSETID = (
    SELECT TRUNC((rn + 2 -1)/2)
    FROM (
        SELECT 
            BU_NAME,
            ROW_NUMBER() OVER(ORDER BY BU_NAME) rn
        FROM PRICELIST_SRC_TBL
        WHERE VAL_STATUS='Y'
    ) ps
    WHERE ps.BU_NAME = pt.BU_NAME
)
WHERE EXISTS (
    SELECT 1 
    FROM PRICELIST_SRC_TBL ps 
    WHERE ps.BU_NAME = pt.BU_NAME AND ps.VAL_STATUS='Y'
);

该方案通过子查询计算行号与目标值，再关联更新，效果与MERGE完全一致。

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