使用列级 WHERE 子句更新多列中的所有 SQL NULL 值? [英] Update all SQL NULL values in multiple columns using Column level WHERE clause?

问题描述

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

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.

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

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?

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

Here is a sample query showing a few of the records from 4 different tables:

推荐答案

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

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

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

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

But you could use this in the UPDATE statement:

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

逻辑是只有当列值为 NULL 时，值才会更新为 99，这是因为 COALESCE 的工作方式——返回第一个非 NULL 值(从左到右处理提供的列表).

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).

这篇关于使用列级 WHERE 子句更新多列中的所有 SQL NULL 值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！