如何将同组非NULL的customerID更新至对应NULL行？

我有如下查询语句：

SELECT Number 
FROM customers
GROUP BY Number
HAVING COUNT(*) > 1 
   AND SUM(CASE WHEN customerID IS NULL THEN 1 END) > 0
   AND SUM(CASE WHEN customerID IS NOT NULL THEN 1 END) > 0

该查询返回的分组满足：

• 每组至少包含一行customerID为NULL的记录，以及至少一行customerID非NULL的记录
• 每组内所有非NULL的customerID值均相同

我需要编写一条UPDATE语句，将每组中customerID为NULL的行，更新为同组内非NULL的customerID值。

当前数据 #

期望更新后的数据 #

解决方案 #

方法1：子查询关联更新（通用SQL写法） #

UPDATE customers c
SET customerID = (
    SELECT MAX(customerID)
    FROM customers
    WHERE Number = c.Number
      AND customerID IS NOT NULL
)
WHERE c.customerID IS NULL
  AND EXISTS (
    SELECT 1
    FROM customers
    WHERE Number = c.Number
      AND customerID IS NOT NULL
);

说明：

• 子查询通过MAX(customerID)获取当前分组下唯一的非NULL customerID（因题目明确同组非NULL值一致，用MIN/ANY也可）
• WHERE c.customerID IS NULL确保只更新目标行
• EXISTS条件匹配原查询的筛选逻辑，只处理存在有效customerID的分组

方法2：JOIN方式更新（适用于MySQL、PostgreSQL等） #

UPDATE customers c
JOIN (
    SELECT Number, MAX(customerID) AS valid_customerID
    FROM customers
    WHERE customerID IS NOT NULL
    GROUP BY Number
) AS sub ON c.Number = sub.Number
SET c.customerID = sub.valid_customerID
WHERE c.customerID IS NULL;

说明：

• 先通过子查询预计算每个Number对应的有效customerID
• 再通过JOIN关联原表更新，数据量大时效率优于子查询写法

方法3：窗口函数方式（适用于PostgreSQL、SQL Server等） #

UPDATE customers
SET customerID = FIRST_VALUE(customerID) OVER (PARTITION BY Number ORDER BY customerID IS NULL)
WHERE customerID IS NULL;

说明：

• ORDER BY customerID IS NULL会将非NULL值排在分组最前，FIRST_VALUE直接取该值
• 写法简洁，无需额外关联操作

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