Update all SQL NULL values in multiple columns using Column level WHERE clause?

Question

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.

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?

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

Answer 1

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

But you could use this in the UPDATE statement:

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

Answer 2

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.

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

Answer 3

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='update your table set ';x=0;while(x <= 40){sql += 'answer_'+x+ ' = coalesce(answer_'+x+',99),\n';x++;};alert(sql);

Answer 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?

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.