SQL MERGE执行失败处理：按指定规则更新TARGET_TABLE数据

针对你的需求，核心是解决MERGE时的多匹配问题，同时满足ORDER_ID=2的取值与ORDER_ID=1不同的约束。以下是两种可行的实现方案：

方案一：分步确定取值（通用SQL兼容） #

通过CTE先给每个ORDER_ID的NEW_VALUE随机排序，再强制修正ORDER_ID=2的取值：

WITH ordered_new_values AS (
    SELECT 
        order_id,
        new_value,
        -- 给每个ORDER_ID的NEW_VALUE生成随机排序序号
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY DBMS_RANDOM.VALUE) AS rn
    FROM temp_table
),
selected_values AS (
    -- 先随机选每个ORDER_ID的第一个值
    SELECT 
        order_id,
        new_value
    FROM ordered_new_values
    WHERE rn = 1
    -- 覆盖ORDER_ID=2的取值，确保和ORDER_ID=1不同
    UNION ALL
    SELECT 
        2 AS order_id,
        CASE 
            WHEN (SELECT new_value FROM selected_values WHERE order_id = 1) = 'DEF' THEN 'GHI'
            ELSE 'DEF'
        END AS new_value
    FROM dual
    WHERE EXISTS (SELECT 1 FROM target_table WHERE order_id = 2)
)
MERGE INTO target_table tt
USING (
    -- 去重，避免ORDER_ID=2出现重复记录
    SELECT DISTINCT order_id, new_value
    FROM selected_values
) test
ON (test.order_id = tt.order_id)
WHEN MATCHED THEN 
    UPDATE SET tt.old_value = test.new_value;

逻辑说明 #

1. ordered_new_values：给每个ORDER_ID的两个NEW_VALUE随机分配序号，rn=1代表随机选中的一个值。
2. selected_values：先取每个ORDER_ID的随机值，再单独处理ORDER_ID=2，强制它选择与ORDER_ID=1不同的取值。
3. 最终MERGE时使用去重后的取值集合，避免多对多匹配导致的错误。

方案二：关联ORDER_ID=1的选择结果（更简洁） #

先随机确定ORDER_ID=1的取值，再基于这个结果过滤ORDER_ID=2的可选值：

WITH order1_selection AS (
    -- 随机选ORDER_ID=1的一个NEW_VALUE
    SELECT new_value AS selected_val
    FROM temp_table
    WHERE order_id = 1
    ORDER BY DBMS_RANDOM.VALUE
    FETCH FIRST 1 ROW ONLY
)
MERGE INTO target_table tt
USING (
    SELECT 
        t.order_id,
        CASE 
            WHEN t.order_id = 2 THEN 
                -- 强制ORDER_ID=2选和ORDER_ID=1不同的值
                CASE WHEN os.selected_val = 'DEF' THEN 'GHI' ELSE 'DEF' END
            ELSE t.new_value
        END AS final_new_val
    FROM temp_table t
    CROSS JOIN order1_selection os
    -- 对ORDER_ID=2，只保留与ORDER_ID=1不同的取值
    WHERE (t.order_id != 2 OR t.new_value != os.selected_val)
    -- 每个ORDER_ID只保留一行随机结果
    QUALIFY ROW_NUMBER() OVER (PARTITION BY t.order_id ORDER BY DBMS_RANDOM.VALUE) = 1
) test
ON (test.order_id = tt.order_id)
WHEN MATCHED THEN 
    UPDATE SET tt.old_value = test.final_new_val;

逻辑说明 #

1. order1_selection：随机获取ORDER_ID=1的一个NEW_VALUE。
2. USING子句中：
   • 对ORDER_ID=2，直接计算出与ORDER_ID=1不同的取值；
   • 对其他ORDER_ID，随机选一个NEW_VALUE；
   • 通过QUALIFY确保每个ORDER_ID仅返回一行，彻底避免MERGE的多匹配问题。

注意事项 #

• 不同数据库的随机函数不同：Oracle用DBMS_RANDOM.VALUE，PostgreSQL用RANDOM()，SQL Server用NEWID()替换排序条件即可。
• 假设TEMP_TABLE中每个ORDER_ID仅包含DEF和GHI两个NEW_VALUE，若有更多值，需要调整CASE逻辑来匹配需求。

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