同ID多行场景下TARGET表更新报错解决：Merge/Update/CTE实现方案

当你尝试用SOURCE表更新TARGET表时，出现SINGLE ROW SUBQUERY RETURNING MULTPILE VALUES错误，核心原因是同一个ID对应多行数据，直接通过ID关联会导致子查询返回多个值。下面提供三种基于Oracle的解决方案，通过行号匹配实现精准的逐行更新：

测试数据准备 #

先创建并插入示例数据，方便验证效果：

-- 创建SOURCE表
CREATE TABLE SOURCE (ID NUMBER, DESCRIPTION VARCHAR2(50));
INSERT INTO SOURCE VALUES (123, 'PAIN');
INSERT INTO SOURCE VALUES (123, 'FEELING NERVOUS');
INSERT INTO SOURCE VALUES (123, 'NECK PAIN');

-- 创建TARGET表
CREATE TABLE TARGET (ID NUMBER, DESCRIPTION VARCHAR2(50));
INSERT INTO TARGET VALUES (123, NULL);
INSERT INTO TARGET VALUES (123, NULL);
INSERT INTO TARGET VALUES (123, NULL);

方案一：使用MERGE语句（推荐） #

通过给两张表的同ID行添加行号，用ID+行号作为关联条件，实现精准匹配更新：

MERGE INTO TARGET t
USING (
    SELECT 
        ID, 
        DESCRIPTION,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DESCRIPTION) AS rn
    FROM SOURCE
) s
ON (
    t.ID = s.ID 
    AND ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY t.DESCRIPTION) = s.rn
)
WHEN MATCHED THEN
    UPDATE SET t.DESCRIPTION = s.DESCRIPTION;

方案二：使用CTE+UPDATE语句 #

先通过CTE给两张表添加行号，再基于行号关联更新：

WITH target_rn AS (
    SELECT 
        ID, 
        DESCRIPTION,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DESCRIPTION) AS rn,
        ROWID AS rid  -- 用ROWID定位行
    FROM TARGET
),
source_rn AS (
    SELECT 
        ID, 
        DESCRIPTION,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DESCRIPTION) AS rn
    FROM SOURCE
)
UPDATE target_rn t
SET t.DESCRIPTION = (
    SELECT s.DESCRIPTION 
    FROM source_rn s 
    WHERE s.ID = t.ID AND s.rn = t.rn
)
WHERE EXISTS (
    SELECT 1 
    FROM source_rn s 
    WHERE s.ID = t.ID AND s.rn = t.rn
);

方案三：Oracle 12c+ 用LATERAL子查询更新 #

适合高版本Oracle，通过子查询生成行号并匹配：

UPDATE TARGET t
SET DESCRIPTION = (
    SELECT s.DESCRIPTION
    FROM (
        SELECT 
            DESCRIPTION,
            ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DESCRIPTION) AS rn
        FROM SOURCE
        WHERE ID = t.ID
    ) s
    WHERE s.rn = (
        SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DESCRIPTION)
        FROM TARGET
        WHERE ROWID = t.ROWID
    )
);

关键注意事项 #

• 排序字段要明确：如果同ID的行需要按特定顺序匹配（比如插入顺序），一定要在ROW_NUMBER()的ORDER BY子句中指定可靠的排序字段（比如创建时间、主键列），避免默认排序导致匹配错误。
• 先验证再更新：执行更新前，建议先运行以下查询确认行匹配是否正确：

SELECT 
    t.ID,
    t.DESCRIPTION AS target_old_desc,
    s.DESCRIPTION AS source_new_desc,
    t.rn AS target_row_num,
    s.rn AS source_row_num
FROM (
    SELECT ID, DESCRIPTION, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DESCRIPTION) AS rn FROM TARGET
) t
JOIN (
    SELECT ID, DESCRIPTION, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DESCRIPTION) AS rn FROM SOURCE
) s ON t.ID = s.ID AND t.rn = s.rn;

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