如何用SQL将表A现有列填充为表B对应列的首个匹配值？

现有表A，包含主键列id和contact_name列，该列所有值目前均为NULL。另有表B，包含contact_name列和ref_id列，B表的ref_id与A表的id一一对应，但B表中可能存在多个ref_id相同的行。

示例数据：
表A（初始状态）

id | contact_name
1  | NULL
2  | NULL

表B

ref_id | contact_name
1      | "John"
2      | "Helen"
2      | "Alex"

要求：在不新增或修改两表其他行的前提下，将表A的contact_name列填充为B表中对应ref_id匹配的首个contact_name值，最终表A结果如下：

id | contact_name
1  | "John"
2  | "Helen"

1. MySQL（8.0+ 支持窗口函数） #

使用ROW_NUMBER()窗口函数为每个ref_id的行排序，取排序后的第一行数据更新表A：

UPDATE tableA a
JOIN (
    SELECT 
        ref_id,
        contact_name,
        ROW_NUMBER() OVER (PARTITION BY ref_id ORDER BY (SELECT 0)) AS rn
    FROM tableB
) b ON a.id = b.ref_id AND b.rn = 1
SET a.contact_name = b.contact_name;

注：ORDER BY (SELECT 0)是利用MySQL特性，不指定具体排序字段时按数据存储的物理顺序取第一行；若有明确排序规则（如按创建时间），可替换为对应字段，比如ORDER BY create_time ASC。

2. PostgreSQL #

通过窗口函数筛选每个ref_id的首行，再关联更新：

UPDATE tableA a
SET contact_name = b.contact_name
FROM (
    SELECT 
        ref_id,
        contact_name,
        ROW_NUMBER() OVER (PARTITION BY ref_id ORDER BY ctid) AS rn
    FROM tableB
) b
WHERE a.id = b.ref_id AND b.rn = 1;

注：ctid是PostgreSQL标识行物理位置的系统字段，用来取存储顺序的第一行，也可替换为业务排序字段。

3. SQL Server #

方法一：窗口函数

UPDATE a
SET a.contact_name = b.contact_name
FROM tableA a
INNER JOIN (
    SELECT 
        ref_id,
        contact_name,
        ROW_NUMBER() OVER (PARTITION BY ref_id ORDER BY (SELECT NULL)) AS rn
    FROM tableB
) b ON a.id = b.ref_id AND b.rn = 1;

方法二：TOP 1 WITH TIES

UPDATE a
SET a.contact_name = b.contact_name
FROM tableA a
INNER JOIN (
    SELECT TOP 1 WITH TIES
        ref_id,
        contact_name
    FROM tableB
    ORDER BY ROW_NUMBER() OVER (PARTITION BY ref_id ORDER BY (SELECT NULL))
) b ON a.id = b.ref_id;

4. Oracle #

使用MERGE结合窗口函数实现更新：

MERGE INTO tableA a
USING (
    SELECT 
        ref_id,
        contact_name,
        ROW_NUMBER() OVER (PARTITION BY ref_id ORDER BY ROWID) AS rn
    FROM tableB
) b
ON (a.id = b.ref_id AND b.rn = 1)
WHEN MATCHED THEN UPDATE SET a.contact_name = b.contact_name;

注：ROWID是Oracle中行的唯一物理标识符，用来取存储顺序的第一行，也可替换为业务排序字段。

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