如何用关联表中首个非空名称更新大表的name字段？

要实现将Table a的name替换为Table b中对应id的首个非空name，核心是先筛选出每个table_a_id对应的第一条有效name，再关联更新。以下是具体实现方案：

步骤1：筛选每个table_a_id的目标名称 #

先通过子查询+窗口函数，获取每个table_a_id对应的首个非空name（按Table b的记录id顺序排序，若需按语言优先级调整，修改ORDER BY字段即可）：

SELECT 
    table_a_id,
    name AS target_name
FROM (
    SELECT 
        table_a_id,
        name,
        -- 按table_a_id分组，每组内按记录id排序，标记行号
        ROW_NUMBER() OVER (PARTITION BY table_a_id ORDER BY id) AS rn
    FROM Table_b
    -- 过滤空名称（同时处理NULL和空字符串）
    WHERE name IS NOT NULL AND name != ''
) AS filtered
-- 取每组的第一条记录
WHERE rn = 1;

步骤2：关联更新Table a #

将上述查询结果与Table a关联，执行更新操作：

PostgreSQL/SQL Server 写法 #

UPDATE Table_a
SET name = target.target_name
FROM Table_a
JOIN (
    SELECT 
        table_a_id,
        name AS target_name
    FROM (
        SELECT 
            table_a_id,
            name,
            ROW_NUMBER() OVER (PARTITION BY table_a_id ORDER BY id) AS rn
        FROM Table_b
        WHERE name IS NOT NULL AND name != ''
    ) AS filtered
    WHERE rn = 1
) AS target ON Table_a.id = target.table_a_id;

MySQL 写法 #

MySQL的UPDATE JOIN语法略有不同：

UPDATE Table_a
JOIN (
    SELECT 
        table_a_id,
        name AS target_name
    FROM (
        SELECT 
            table_a_id,
            name,
            ROW_NUMBER() OVER (PARTITION BY table_a_id ORDER BY id) AS rn
        FROM Table_b
        WHERE name IS NOT NULL AND name != ''
    ) AS filtered
    WHERE rn = 1
) AS target ON Table_a.id = target.table_a_id
SET Table_a.name = target.target_name;

说明 #

• 如果需要按语言优先级（比如优先取lang_id=1的名称，再取lang_id=2），只需将子查询中的ORDER BY id改为ORDER BY lang_id即可。
• 若Table a中某条记录在Table b中没有对应非空name，该记录的name会保持原有值，不会被修改。

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