关于在DBO中为每个ID的首个NULL字段添加Code Z987的技术求助

假设你的表结构包含ID列和多个顺序排列的Code列（比如Code1、Code2、Code3...），以下是两种可行的解决方案：

静态列场景（列数固定） #

如果你的Code列数量固定，直接用带CASE判断的UPDATE语句即可，逻辑是按列的顺序，优先填充最靠前的NULL列：

UPDATE YourTable
SET 
    Code1 = CASE WHEN Code1 IS NULL THEN 'Z987' ELSE Code1 END,
    Code2 = CASE WHEN Code1 IS NOT NULL AND Code2 IS NULL THEN 'Z987' ELSE Code2 END,
    Code3 = CASE WHEN Code1 IS NOT NULL AND Code2 IS NOT NULL AND Code3 IS NULL THEN 'Z987' ELSE Code3 END,
    Code4 = CASE WHEN Code1 IS NOT NULL AND Code2 IS NOT NULL AND Code3 IS NOT NULL AND Code4 IS NULL THEN 'Z987' ELSE Code4 END
WHERE 
    Code1 IS NULL OR Code2 IS NULL OR Code3 IS NULL OR Code4 IS NULL;

逻辑说明 #

• 每一个CASE语句都会检查当前列是否是该ID下的第一个NULL列（即前面所有Code列都已填充）
• WHERE子句过滤掉所有Code列都已填满的行，避免无意义的更新操作

动态列场景（列数不固定或较多） #

如果你的Code列数量较多或者可能变动，可以用动态SQL自动生成更新语句，无需手动编写重复逻辑：

DECLARE @cols NVARCHAR(MAX), @updateSQL NVARCHAR(MAX);

-- 获取所有以Code开头的列名
SELECT @cols = STRING_AGG(QUOTENAME(name), ', ')
FROM sys.columns
WHERE object_id = OBJECT_ID('YourTable') AND name LIKE 'Code%';

-- 生成UPDATE的SET子句部分
SELECT @updateSQL = N'UPDATE YourTable SET ' + STRING_AGG(
    QUOTENAME(name) + N' = CASE WHEN ' + 
    (SELECT STRING_AGG(QUOTENAME(prev_name) + N' IS NOT NULL', ' AND ') 
     FROM (SELECT LAG(name) OVER (ORDER BY column_id) AS prev_name 
           FROM sys.columns 
           WHERE object_id = OBJECT_ID('YourTable') AND name LIKE 'Code%' 
           AND column_id <= t.column_id) sub
     WHERE prev_name IS NOT NULL) + 
    N' AND ' + QUOTENAME(name) + N' IS NULL THEN ''Z987'' ELSE ' + QUOTENAME(name) + N' END',
    ', '
) WITHIN GROUP (ORDER BY column_id)
FROM sys.columns t
WHERE object_id = OBJECT_ID('YourTable') AND name LIKE 'Code%';

-- 添加WHERE子句，过滤存在NULL列的行
SET @updateSQL = @updateSQL + N' WHERE EXISTS (SELECT 1 FROM YourTable t WHERE ' + STRING_AGG(QUOTENAME(name) + N' IS NULL', ' OR ') + N')';

-- 先打印语句验证正确性，确认无误后再执行
PRINT @updateSQL;
-- EXEC sp_executesql @updateSQL;

关键注意事项 #

• 操作前务必备份数据，或者先运行以下SELECT语句验证需要更新的行：

  SELECT ID, Code1, Code2, Code3, Code4
  FROM YourTable
  WHERE Code1 IS NULL OR Code2 IS NULL OR Code3 IS NULL OR Code4 IS NULL;
  

• 动态SQL执行前一定要用PRINT输出语句，确认生成的逻辑符合预期，避免误更新

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