Update all empty cells to NULL?
-
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
-
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! -
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.
-
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...
-
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! -
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
-
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! -
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
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply