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

We have a database with a bunch of wide tables (40-80 columns each) and just found a bug that introduced NULL values into about 500 of the records. The NULL values can appear in any of the columns (all are integer columns, see image below) but these NULL values are causing issues with one of our reporting systems that cannot be changed easily. We need to replace the NULL values with a specific static value (in this case 99), but since this change has to be made on a per-column basis for over 250 different columns I would rather not write individual TSQL scripts updating each column one by one.

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

My brain is too fried right now to think up a clever solution, so my question is how can I perform this task on all columns on a table (or better yet multiple tables) using a simple and readable SQL query. I can isolate the records easy enough using a chain of WHERE (Answer_1 IS NULL) OR (Answer_2 IS NULL) OR ... or even by AdministrationID numbers for each table, but this trick won't work when updating as where clause is per row not per column. Any advice?

我的大脑现在太过于思考一个聪明的解决方案，所以我的问题是如何使用简单易读的SQL查询在表（或更好的多个表）上的所有列上执行此任务。我可以使用一个WHERE链（Answer_1 IS NULL）或（Answer_2 IS NULL）或者......甚至每个表的AdministrationID编号来容易地隔离记录，但是当更新为where子句时，这个技巧将不起作用每列不行。任何建议？

Here is a sample query showing a few of the records from 4 different tables: 使用列级WHERE子句更新多列中的所有SQL NULL值？

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

4 个解决方案

#1

There isn't any convention to this -- if you want to only process records where respective columns are NULL, you need to use:

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

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

But you could use this in the UPDATE statement:

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

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

The logic is that the value will be updated to 99 only if the column value is NULL, because of how COALESCE works--returning the first non-NULL value (processing the list provided from left to right).

逻辑是，只有当列值为NULL时，该值才会更新为99，因为COALESCE的工作方式 - 返回第一个非NULL值（处理从左到右提供的列表）。

#2

Since you have to do this all over the place i wrote some javascript to help you build the sql. cut and paste this into your browsers address bar to get your sql.

因为你必须在整个地方这样做我写了一些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);

#3

Just poll the sys.columns table for each table and create some dynamic sql... It's brute force but it saves you from having to write all the t-sql out.

只需轮询sys.columns表中的每个表并创建一些动态的sql ......这是一种蛮力，但它可以让你不必编写所有的t-sql。

For example:

例如：

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

#4

I don't like the idea to manipulate the data itself for the purpose of reporting. If you change the NULL values to 99 to just to make your reporting easier then the I consider that data as corrupted. What if there are other consumer apart from reporting which need genuine data?

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

I would rather write an intelligent query for the report. For example, if you use ISNULL(columnname, 99), it would return 99 whenever the column value is NULL.

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

#1