如何将Table A的ID更新至Table B，规避单行子查询错误？

常规UPDATE语句用子查询匹配NAME会触发单行子查询错误——因为同一个NAME在Table A中对应多个ID，子查询返回多行无法直接赋值。以下按不同数据库给出可行方案：

MySQL 解法 #

通过JOIN带行号的子查询，让同NAME的行一一对应：

UPDATE `Table B` b
JOIN (
    SELECT NAME, ID, 
           ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY ID) AS rn
    FROM `Table A`
) a ON b.NAME = a.NAME
JOIN (
    SELECT NAME, 
           ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY (SELECT NULL)) AS rn
    FROM `Table B`
) b_rn ON b.NAME = b_rn.NAME AND b_rn.rn = a.rn
SET b.ID = a.ID;

SQL Server 解法 #

用CTE给两张表的同NAME行添加行号后关联更新：

WITH A_Ranked AS (
    SELECT ID, NAME,
           ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY ID) AS rn
    FROM [Table A]
),
B_Ranked AS (
    SELECT ID, NAME,
           ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY (SELECT NULL)) AS rn
    FROM [Table B]
)
UPDATE B_Ranked
SET ID = A_Ranked.ID
FROM B_Ranked
JOIN A_Ranked ON B_Ranked.NAME = A_Ranked.NAME AND B_Ranked.rn = A_Ranked.rn;

PostgreSQL 解法 #

借助CTE和行号，结合ctid（或主键）定位行更新：

WITH A_Ranked AS (
    SELECT ID, NAME,
           ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY ID) AS rn
    FROM "Table A"
),
B_Ranked AS (
    SELECT ID, NAME,
           ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY (SELECT NULL)) AS rn,
           ctid AS row_id
    FROM "Table B"
)
UPDATE "Table B" b
SET ID = a.ID
FROM B_Ranked br
JOIN A_Ranked a ON br.NAME = a.NAME AND br.rn = a.rn
WHERE b.ctid = br.row_id;

关键说明 #

• 核心逻辑是给同NAME分组内的行添加行号，让Table A和Table B的行能一一匹配，避免单行子查询的多行返回问题。
• 如果Table B有主键，建议用主键代替(SELECT NULL)排序，确保行匹配的稳定性。

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