Updating Null Values In A Table

Question

Post reply

Updating Null Values In A Table

Roger Abram

Hall of Fame

Points: 3683
More actions
June 9, 2009 at 10:15 am

#131734

I have a table with about 150 fields that allow Nulls
I know I can setup a script to change the value just to '' but was wondering if there's an easier method than just having to set up:
Update Table
Set Field1 = '' where Field1 Is Null
Update Table
Set Field2 = '' where Field2 Is Null
.... etc etc.
Any type of global script I can run?
Thank you.
Roger

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you'll want to use the metadata to generate the scripts for you;

something like this:

SELECT

'UPDATE ' + SYSOBJECTS.NAME + ' SET ' + SYSCOLUMNS.NAME + ' = '''' WHERE ' + SYSCOLUMNS.NAME + ' IS NULL'

FROM SYSOBJECTS

INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID

WHERE SYSOBJECTS.XTYPE='U'

AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')

--AND SYSOBJECTS.NAME = 'YourTableName'

--example results

UPDATE Principal_Place_of_Performance_Award SET Place_Congressional_District_Modified = '' WHERE Place_Congressional_District_Modified IS NULL

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

john.arnott SSChampion Points: 11882 More actions · Answer 2

Since all the columns are in the same table, you may want to consider using a single UPDATE with multiple SET arguments. You could use a modified version of Lowell's script that accesses metadata to build your SQL, but just to get all the field name lines.

SELECT

' , ' + SYSCOLUMNS.NAME + ' = Coalesce(' + SYSCOLUMNS.NAME + ','''')'

FROM SYSOBJECTS

INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID

WHERE SYSOBJECTS.XTYPE='U'

AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')

AND SYSOBJECTS.NAME = 'TMP1'With a test table set up with this....Create table TMP1 (fld_A int identity

,fld_1 char(10) null

,fld_2 char(10) null

,fld_3 char(10) null

,fld_4 char(10) null

,fld_5 char(10) null)

Insert TMP1

Select 'Something','more', null, null, null union all

Select null, 'Wow', null, null, null union all

Select 'Else', 'nUlL', null, null, null union all

Select null, null, null, null, null union all

Select 'John',null, null, null, null union all

Select '',null, null, null, null

Sample output would be....

, fld_1 = Coalesce(fld_1,'')

, fld_2 = Coalesce(fld_2,'')

, fld_3 = Coalesce(fld_3,'')

, fld_4 = Coalesce(fld_4,'')

, fld_5 = Coalesce(fld_5,'')

Lop off the first comma and append this to an UPDATE.... SET, and you'll have ssomething like this:Update TMP1 SET

fld_1 = Coalesce(fld_1,'')

, fld_2 = Coalesce(fld_2,'')

, fld_3 = Coalesce(fld_3,'')

, fld_4 = Coalesce(fld_4,'')

, fld_5 = Coalesce(fld_5,'')