October 8, 2007 at 2:04 pm
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
October 8, 2007 at 2:08 pm
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
October 8, 2007 at 2:59 pm
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.
October 8, 2007 at 3:04 pm
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...
October 8, 2007 at 3:18 pm
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
October 8, 2007 at 5:22 pm
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
October 8, 2007 at 5:41 pm
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
October 9, 2007 at 6:05 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy