Update all empty cells to NULL?

Question

Post reply

Update all empty cells to NULL?

monquatch

Old Hand

Points: 364
More actions
October 8, 2007 at 2:04 pm

#76794

Hi,
Is it possible to do an update to a table that sets all empty cells to a NULL value? By 'all empty cells' I mean every column in every row that contains no value (is empty)...
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

depends on what you mean by empty...chances are , it already is null, but it depends on the data.

does empty mean an empty string?

SELECT * from sometable WHERE somecol=''

in that case it would be

UPDATE sometable SET somecol = NULL WHERE somecol=''

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!

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

You'd have to include a SET for every column

update xx

set colA = null

where colA = ''

update xx

set colB = null

where colB = ''

etc.

You could build a set of queries dynamically to do this by querying information_schema.columns for the table name.

monquatch Old Hand Points: 364 More actions · Answer 3

Thanks for the input.

Oh, yes, I did mean an empty string. The data is in fact that, and not already a NULL value.

>>>UPDATE sometable SET somecol = NULL WHERE somecol=''

What I'm looking for is a query that updates not a particular column, but one that updates all column cells where the value is null. Something like, "UPDATE EVERY TABLE CELL WHERE TABLE CELL = ''".

For a table with 100's of columns, it's a headache to run the above query for each column in order to trim up and scale down the table.

Still wondering if it's possible...

Lowell SSC Guru Points: 323518 More actions · Answer 4

you could loop thru syscolumns and find all the varchars and such, and build a sql statement to run:

select 'UPDATE ' + object_name(id) + ' SET ' + name + ' = NULL WHERE ' + name + ' = ''''' ASSQLStatement

from syscolumns where type_name(xtype) in('varchar','char','nvarchar','nchar')

and object_name(id) not like 'sys%'

results:

UPDATE CAT_UpdateShippingRate SET @Country = NULL WHERE @Country = ''

UPDATE CAT_UpdateShippingRate SET @StateProvince = NULL WHERE @StateProvince = ''

UPDATE CAT_UpdateShippingRate SET @AdjustRate = NULL WHERE @AdjustRate = ''

UPDATE CAT_UpdateShippingRate SET @ShipTime = NULL WHERE @ShipTime = ''

UPDATE storm_Forum_PMAdd SET @RemoteAddr = NULL WHERE @RemoteAddr = ''

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!

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 5

I'd use something like Lowell has to generate a script. Be sure it's hitting the tables that you really need. Last thing you want to do is update something you didn't mean to with this.

And take a backup before you do this 😉

Lowell SSC Guru Points: 323518 More actions · Answer 6

Steve Jones - Editor (10/8/2007)
I'd use something like Lowell has to generate a script. Be sure it's hitting the tables that you really need. Last thing you want to do is update something you didn't mean to with this.
And take a backup before you do this 😉

yeah this would definitely step on a lot of data and maybe produce unexpected results; also note, some applications that grab this data might not take too kindly to nulls; in vb6 for example, you get errors when you do something like Textbox1.Text = Recordset("Sometextfield") <<--error if 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 Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

I would recommend using INFORMATION_SCHEMA.COLUMNS instead of syscolumns, since it doesn't rely on system tables, which may change from version to version (or even service pack to service pack). And don't forget to include a check for column nullability, since you'll get an error if you try to set a value to NULL in a non-nullable column.

John