How to update all columns that are NULL to empty string?

Question

I want to update my table so that every column that has a value of NULL is updated to be and empty string.

Currently I have the following query, but it would only update one column and I want to update all columns that are NULL to empty string.

UPDATE table SET column1='' WHERE column1 IS NULL

Answer 1

You can update multiple columns with one statement by doing something like this:

UPDATE table SET column1='', column2='', column3='' WHERE column1 IS NULL

HOWEVER thsi will only update based on the where clause.

For what you are trying to do, you'll need separate statements.

UPDATE table SET column1='' WHERE column1 IS NULL
UPDATE table SET column2='' WHERE column2 IS NULL
UPDATE table SET column3='' WHERE column3 IS NULL

EDIT Try this:

UPDATE table SET column1= IfNull(column1,''), column2= IfNull(column2,'') , column3= IfNull(column3,'') 

Answer 2

You can update a column to itself and check for null there...

UPDATE table SET 
column1 = ISNULL(column1,''),
column2 = ISNULL(column2,''),
column3 = ISNULL(column3,'')

etc..

No WHERE clause needed because you want it to run on all records.

Answer 3

Actually you can do something like this

DECLARE @sql varchar(max)=''

select @sql= @sql+''+ c.name + '= CASE WHEN ' +c.name+'=''''THEN NULL ELSE ' +c.name+' end,
'
from sys.tables t
JOIN sys.columns c
ON t.object_id = c.object_id
WHERE t.object_id = 1045578763 -- Your object_id table


PRINT 'UPDATE <TABLE>
        SET '+@sql