如何按DOCNUM批量更新数据表中TYPE列的NULL值？

需求说明 #

将表中每个DOCNUM分组下的TYPE列NULL值，替换为该组已存在的stockin或stockout非空TYPE值。

各数据库实现方法 #

1. MySQL/MariaDB

使用JOIN关联子查询的方式高效更新：

UPDATE your_table t1
JOIN (
    -- 提取每个DOCNUM对应的非空TYPE值
    SELECT DOCNUM, TYPE
    FROM your_table
    WHERE TYPE IS NOT NULL
    GROUP BY DOCNUM, TYPE
) t2 ON t1.DOCNUM = t2.DOCNUM
SET t1.TYPE = t2.TYPE
WHERE t1.TYPE IS NULL;

2. SQL Server

通过CTE（公共表表达式）映射DOCNUM与TYPE的对应关系后更新：

WITH DocTypeMap AS (
    SELECT DISTINCT DOCNUM, TYPE
    FROM your_table
    WHERE TYPE IS NOT NULL
)
UPDATE t1
SET t1.TYPE = dtm.TYPE
FROM your_table t1
JOIN DocTypeMap dtm ON t1.DOCNUM = dtm.DOCNUM
WHERE t1.TYPE IS NULL;

3. PostgreSQL

使用UPDATE FROM语法完成更新：

UPDATE your_table t1
SET TYPE = t2.TYPE
FROM (
    SELECT DISTINCT DOCNUM, TYPE
    FROM your_table
    WHERE TYPE IS NOT NULL
) t2
WHERE t1.DOCNUM = t2.DOCNUM
AND t1.TYPE IS NULL;

注意事项 #

• 确保每个DOCNUM对应的非空TYPE值唯一，如果同一DOCNUM存在多个不同的非空TYPE（比如同时有stockin和stockout），需先处理数据冲突，否则更新结果会不确定。
• 操作前建议先验证更新范围，避免误改：

  -- 查看即将被更新的行及目标值
  SELECT t1.*, t2.TYPE AS target_type
  FROM your_table t1
  JOIN (
      SELECT DOCNUM, TYPE
      FROM your_table
      WHERE TYPE IS NOT NULL
      GROUP BY DOCNUM, TYPE
  ) t2 ON t1.DOCNUM = t2.DOCNUM
  WHERE t1.TYPE IS NULL;
  

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