如何编写UPDATE语句，通过多列匹配用现有记录填充缺失数据

某表存在数千条maj_id和maj_name列数据缺失的记录，需要通过匹配parent_name、parent_id、parent_id_2三列，利用同组内已有对应数据的记录填充这些缺失值。

示例数据 #

已实现的待更新记录定位查询 #

用户已通过以下SQL定位出存在缺失值且同组有有效数据的记录：

select t.parent_id
, t.maj_name
from test_table t
inner join
(
select parent_id
, parent_name
, parent_id_2
from test_table
group by parent_id, parent_name, parent_id_2
having sum(case when maj_name is not null then 1 else 0 end) >= 1
and sum(case when maj_name is null then 1 else 0 end) >= 1
)D
on t.parent_id = d.parent_id and t.parent_name = d.parent_name and t.parent_id_2 = d.parent_id_2
order by parent_id, maj_name ASC;

批量更新缺失值的SQL语句 #

根据不同数据库类型，提供对应的UPDATE语句：

1. MySQL/MariaDB #

使用多表更新语法，先获取每组的有效maj_id和maj_name，再关联更新缺失记录：

UPDATE test_table t
JOIN (
    SELECT parent_id, parent_name, parent_id_2, MAX(maj_id) AS fill_maj_id, MAX(maj_name) AS fill_maj_name
    FROM test_table
    WHERE maj_id IS NOT NULL AND maj_name IS NOT NULL
    GROUP BY parent_id, parent_name, parent_id_2
) AS fill_data
ON t.parent_id = fill_data.parent_id 
AND t.parent_name = fill_data.parent_name 
AND t.parent_id_2 = fill_data.parent_id_2
SET t.maj_id = fill_data.fill_maj_id, t.maj_name = fill_data.fill_maj_name
WHERE t.maj_id IS NULL OR t.maj_name IS NULL;

注：使用MAX()是确保每组只取一个有效值，若同组内有效记录的maj_id和maj_name完全一致，用MIN()或直接取任意一个结果都一样。

2. SQL Server #

使用CTE（公共表表达式）先获取每组的填充值，再执行更新：

WITH fill_data AS (
    SELECT parent_id, parent_name, parent_id_2, 
           MAX(maj_id) OVER (PARTITION BY parent_id, parent_name, parent_id_2) AS fill_maj_id,
           MAX(maj_name) OVER (PARTITION BY parent_id, parent_name, parent_id_2) AS fill_maj_name
    FROM test_table
)
UPDATE t
SET t.maj_id = fd.fill_maj_id, t.maj_name = fd.fill_maj_name
FROM test_table t
JOIN fill_data fd ON t.parent_id = fd.parent_id 
                  AND t.parent_name = fd.parent_name 
                  AND t.parent_id_2 = fd.parent_id_2
WHERE t.maj_id IS NULL OR t.maj_name IS NULL;

3. PostgreSQL #

使用FROM子句关联填充数据进行更新：

UPDATE test_table t
SET maj_id = fill_data.fill_maj_id, maj_name = fill_data.fill_maj_name
FROM (
    SELECT parent_id, parent_name, parent_id_2, 
           MAX(maj_id) AS fill_maj_id, MAX(maj_name) AS fill_maj_name
    FROM test_table
    WHERE maj_id IS NOT NULL AND maj_name IS NOT NULL
    GROUP BY parent_id, parent_name, parent_id_2
) AS fill_data
WHERE t.parent_id = fill_data.parent_id 
  AND t.parent_name = fill_data.parent_name 
  AND t.parent_id_2 = fill_data.parent_id_2
  AND (t.maj_id IS NULL OR t.maj_name IS NULL);

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