How to update all columns that are NULL for empty rows?

Question

I want to update my table so that every NULL column is updated to an empty row.

I currently have the following query, but it will only update one column, and I want to update all columns that are NULL for empty rows.

UPDATE table SET column1='' WHERE column1 IS NULL

Answer 1

You can update multiple columns with a single expression by doing something like this:

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

HOWEVER thsi will only be updated based on the where clause.

For what you are trying to do, you will need separate instructions.

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 the following:

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

Answer 2

You can update the column for yourself and check there is zero ...

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

etc..

WHERE, , .

Answer 3

-

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

Answer 4

You cannot dynamically create statements in standard SQL. Without external programming language help, you will need to repeat this instruction for each column.

Answer 5

You need to specify the columns that you want to change. Therefore, if your table had four columns, you need several queries, for example:

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

It gets a little easier if you just want to set all the columns to an empty row, provided that only one of the columns is NULL, but it may not be the way you want:

UPDATE table SET column1 = '', column2 = '', column3 = '', column4 = ''
WHERE (column1 IS NULL
OR column2 IS NULL
OR column3 IS NULL
OR column4 IS NULL);