Replace all null values of a schema with 0

Question

I need to update all null values of a specific SCHEMA with 0. What is the easiest way of doing it, since I have many tables/columns?

Sample data:

CREATE TABLE [mySchema].[test1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ParentID] [int] NULL,
    [ClassCode] [int] NULL,
    [ClassValue] [int] NULL,
 CONSTRAINT [PK_test1] PRIMARY KEY CLUSTERED ([ID] ASC)
) 

CREATE TABLE [mySchema].[test2](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ParentID] [int] NULL,
    [ClassCode] [int] NULL,
    [ClassValue] [int] NULL,
 CONSTRAINT [PK_test2] PRIMARY KEY CLUSTERED ([ID] ASC)
) 

INSERT INTO test1 (ParentID,ClassCode,ClassValue) VALUES (100,1,NULL)
INSERT INTO test1 (ParentID,ClassCode,ClassValue) VALUES (100,NULL,NULL)
INSERT INTO test1 (ParentID,ClassCode,ClassValue) VALUES (100,1,2)
INSERT INTO test1 (ParentID,ClassCode,ClassValue) VALUES (100,NULL,2)

INSERT INTO test2 (ParentID,ClassCode,ClassValue) VALUES (100,1,NULL)
INSERT INTO test2 (ParentID,ClassCode,ClassValue) VALUES (100,NULL,NULL)
INSERT INTO test2 (ParentID,ClassCode,ClassValue) VALUES (100,1,2)
INSERT INTO test2 (ParentID,ClassCode,ClassValue) VALUES (100,NULL,2)

Result should be:

Test1

+----+----------+-----------+------------+
| ID | ParentID | ClassCode | ClassValue |
+----+----------+-----------+------------+
|  1 |      100 |         1 |          0 |
|  2 |      100 |         0 |          0 |
|  3 |      100 |         1 |          2 |
|  4 |      100 |         0 |          2 |
+----+----------+-----------+------------+

Test 2

+----+----------+-----------+------------+
| ID | ParentID | ClassCode | ClassValue |
+----+----------+-----------+------------+
|  1 |      100 |         1 |          0 |
|  2 |      100 |         0 |          0 |
|  3 |      100 |         1 |          2 |
|  4 |      100 |         0 |          2 |
+----+----------+-----------+------------+

Thank you in advance.

Possible duplicate: https://stackoverflow.com/questions/29926765/how-to-update-all-columns-of-a-record-without-having-to-list-every-column — Tim Biegeleisen, Jul 31 '18 at 07:16
There may not be a silver bullet way to do this. Different columns are generally different beasts, have different types, etc. — Tim Biegeleisen, Jul 31 '18 at 07:17
ISNULL around all your columns. You can even do it dynamically by querying information_Schema. — SqlKindaGuy, Jul 31 '18 at 07:22
Are you sure you want to do this - null indicates no value exists but 0 indicates a value of 0 exists - not the same thing. — P.Salmon, Jul 31 '18 at 07:53
@P.Salmon That's what I said at first, but that's the requirement. — Chriz, Jul 31 '18 at 08:03

Habeeb · Answer 1 · 2018-07-31T09:06:05.877

2

Below script will generate an update statement to update all null values with 0.

SELECT 'UPDATE ' + TABLE_NAME + ' SET [' + COLUMN_NAME + '] = 0 WHERE [' + 
COLUMN_NAME + '] IS NULL'
FROM INFORMATION_SCHEMA.COLUMNS
-- WHERE TABLE_NAME = 'YOURTABLENAME'

Uncomment the last line to do generate the script for a specific table.

edited Jul 31 '18 at 09:06

answered Jul 31 '18 at 07:27

Habeeb

7,601
1
30
33

Thank you for the answer, I have one more question though. This will create all the update statements, how will I execute them?Should I save it to a file and execute the script? – Chriz Jul 31 '18 at 07:48
Hi Chriz, you can run it in SSMS or any other Query Window or run via program. Your choice. – Habeeb Jul 31 '18 at 09:04

Andrea · Accepted Answer · 2018-07-31T08:05:57.523

You can dynamically create the statement from SQL Server metadata table (information_schema.columns) filtering:

by schema (your_specific_schema in the code sample)
by datatype (probably numeric data types)

Here is a sample code:

declare @sql nvarchar(max) =''

select @sql = @sql + 'update [' + table_schema + '].[' + table_name + ']'
              + ' set [' + column_name + '] = 0 where [' + column_name + '] is null' 
              + char(10)
from information_schema.columns
where data_type in ('int','bigint') --ad all numeric types you want to set to 0
    and TABLE_SCHEMA = 'your_specific_schema'
print @sql

Output should look like this:

Once you have checked the generated script you can execute it:

with copy & paste and then with F5
or you can execute the statement directly from the variable @sql appending this statement to the query above:
```
exec (@sql)
```

Replace all null values of a schema with 0

2 Answers2