使用列级 WHERE 子句更新多个列中的所有 SQL NULL 值？

Question

我们有一个包含大量宽表（每个表 40-80 列）的数据库，并且刚刚发现一个错误，该错误将 NULL 值引入到大约 500 条记录中。 NULL 值可以出现在任何列中（所有列都是整数列，请参见下图），但这些 NULL 值会导致我们的报告系统之一出现无法轻松更改的问题。我们需要将 NULL 值替换为特定的静态值（在本例中为 99），但由于必须针对超过 250 个不同的列逐列进行此更改，因此我宁愿不编写单独的 TSQL 脚本来更新每一列一个。

我的大脑现在太混乱了，无法想出一个聪明的解决方案，所以我的问题是如何使用简单且可读的 SQL 查询对表（或者更好的是多个表）上的所有列执行此任务。我可以使用一系列 WHERE (Answer_1 IS NULL) OR (Answer_2 IS NULL) OR ... 甚至每个表的 AdministrationID 编号来轻松隔离记录，但这个技巧不起作用更新时，where 子句是每行而不是每列。有什么建议吗？

下面是一个示例查询，显示了 4 个不同表中的一些记录：

Answer 1

对此没有任何约定 - 如果您只想处理相应列为 NULL 的记录，则需要使用：

WHERE Answer_1 IS NULL 
   OR Answer_2 IS NULL 
   OR ...

但您可以在 UPDATE 语句中使用它：

UPDATE YOUR_TABLE
   SET col1 = COALESCE(col1, 99),
       col2 = COALESCE(col2, 99),
       col3 = ...

逻辑是该值将仅更新为 99如果列值为 NULL，则由于 COALESCE 的工作原理 - 返回第一个非 NULL 值（处理从左到右提供的列表）。

Answer 2

只需轮询每个表的 sys.columns 表并创建一些动态 sql...这是蛮力，但它使您不必写出所有 t-sql。

例如：

DECLARE @TABLENAME AS VARCHAR(255)

SET @TABLENAME = 'ReplaceWithYourTableName'

SELECT 'UPDATE ' + @TableName  + ' SET ' + CAST(Name AS VARCHAR(255)) + ' = 99 
 WHERE ' + CAST(Name AS VARCHAR(255)) + ' IS NULL'
FROM sys.columns 
WHERE object_id = OBJECT_ID(@TABLENAME)
    AND system_type_id = 56 -- int's only

Answer 3

由于您必须在各处执行此操作，因此我编写了一些 javascript 来帮助您构建 sql。将其剪切并粘贴到浏览器地址栏中以获取 sql。

javascript:sql='update your table set ';x=0;while(x <= 40){sql += 'answer_'+x+ ' = coalesce(answer_'+x+',99),\n';x++;};alert(sql);

Answer 4

我不喜欢为了报告的目的而操纵数据本身的想法。如果您将 NULL 值更改为 99 只是为了使报告更容易，那么我认为该数据已损坏。如果除了报告之外还有其他消费者需要真实数据怎么办？

我宁愿为报告编写一个智能查询。例如，如果使用 ISNULL(columnname, 99)，则只要列值为 NULL，它就会返回 99。