Update all fields in a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update all fields in a table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-11-05 : 08:40:18

Mike writes "I have a table with over 200 fields in it. I need to update the table setting each field to null where that field = ''.

UPDATE table
SET fieldA = null
WHERE fieldA = ''

This has to do with how an obscure letter writing program interprets blank fields in th exported ascii file. How can I do this without writing 200 seperate sql statements? I need to include this in a stored procedure in SQL Server 2000.

Thanks"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-05 : 08:43:39

Once again, the CASE expression to the rescue!

UPDATE table
SET fieldA=CASE WHEN fieldA='' THEN Null ELSE fieldA END,
fieldB=CASE WHEN fieldB='' THEN Null ELSE fieldB END,
fieldC=CASE WHEN fieldC='' THEN Null ELSE fieldC END

You don't need a WHERE clause.

Actually, you can also do it this way:

UPDATE table
SET fieldA=NULLIF(fieldA,''),
fieldB=NULLIF(fieldB,''),
fieldC=NULLIF(fieldC,'')

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-13 : 14:27:00

You can do the following

EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

DECLARE @Str VARCHAR(8000)

SET @Str = 'UPDATE TABLE table'

UPDATE c
SET @Str = @Str + ' SET ' + name + ' = NULLIF(' + name + ',''''),'
FROM syscolumns c
WHERE id = OBJECT_ID('table') AND isnullable = 1

SET @Str = LEFT(@Str, LEN(@Str) - 1)

EXEC (@Str)
GO

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

if you do not want to use EXEC you can run the script in query analyzer and instead of EXEC (@Str) you can replace SELECT @Str or PRINT @Str and
paste result pane where ever you want. if this if so for a better formatting do the following:

SET @Str = 'UPDATE TABLE tb' + CHAR(10)

UPDATE c
SET @Str = @Str + 'SET ' + name + ' = NULLIF(' + name + ',''''),' + CHAR(10)
FROM syscolumns c
WHERE id = OBJECT_ID('tb') AND isnullable = 1

SET @Str = LEFT(@Str, LEN(@Str)-2)
SELECT @Str

be awere although that the UPDATE statement does not change any system table
you should use it carefully and do not remember to disable 'allow updates'.

you can do the job at least in to other ways (including cursor bocomes three).
if you are not happy with this solution i can send other ways to you.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2002-11-17 : 05:55:09

Going by the same lines as burbakei, here's one thats less dangerous (it doesnt need you to run sp_configure)

SELECT 'UPDATE table_name SET ' + name + ' = NULLIF(' + name + ', '''') ' FROM syscolumns WHERE id = OBJECT_ID('table_name')

If you run this from Query Analyzer, you will get in the Results pane, one SQL statement per column in your table. Just copy the statments and execute them from the Query Analyzer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources