Way to update every column 2

[seabaz2000]

Hi,
I was just wondering is there any way to update every column in a given table something like this.

UPDATE #TempTableForData1
SET = " "
WHERE VALUE = NULL

This code above would aim to set every column that was null in the table to blank.
I hope I am explaining myself correctly.
Regards

 

[mrdenny]

Nope, you have to do each column one at a time.

update #TempTable1
SET Col1 = ''
WHERE Col1 is null

update #TempTable2
SET Col2 = ''
WHERE Col2 is null

...

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLDenis]

you could do it in 1 shot, but you still have to list them all out

update #TempTable1
SET Col1 = case col1 when null then '' else Col1 end,
SET Col2 = case col2 when null then '' else Col2 end
etc etc etc

next time consider using COALESCE on the insert
INSERT TempTable1 (col1 ,col12)
select coalesce(SomeVal,' '),coalesce(SomeVal2,' ')




Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLDenis]

Ditch the second set I gave you in the answer before
This is correct

update #TempTable1
SET Col1 = case col1 when null then '' else Col1 end,
Col2 = case col2 when null then '' else Col2 end
etc etc etc

another way

update #TempTable1
SET Col1 = coalesce(Col1 ,' '),
Col2 = coalesce(Col2 ,' '),
Col3 = coalesce(Col3 ,' ')
where Col1 is null
or  Col2 is null
or  Col3 is null

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/