使用列级 WHERE 子句更新多列中的所有 SQL NULL 值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3938958/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update all SQL NULL values in multiple columns using Column level WHERE clause?
提问by Greg Bray
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) OR (Answer_2 IS NULL) OR ...甚至是每个表的 AdministrationID 编号，但是这个技巧在更新时不起作用，因为 where 子句是每行而不是每列。有什么建议吗？
Here is a sample query showing a few of the records from 4 different tables:
这是一个示例查询，显示了来自 4 个不同表的一些记录：
回答by OMG Ponies
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 值（处理从左到右提供的列表）。
回答by Hiawatha Tiller
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
回答by jason saldo
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);
回答by Ashish Patel
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。
