July 26, 2012 at 11:49 pm
Hi guys,
I have one object which present 26 columns and in that object there are almost 1000+ rows which have NULL values.
e.g.
pk_Id firstname lastname Phone
1 Sagar NULL NULL
2 NULL Mohite 12345
3 NULL NULL NULL
I have to update NULL values to ''(blank). I don't want to write update query for 26 columns. There are many permutations to check NULL in Where clause. Is there any best way to update NULL values on object directly?
Thanks in advance.
July 27, 2012 at 12:24 am
No. There is no way to update the NULL values in the table directly
You will have to write a query manually for all the 26 columns and document it
One option you can consider is to use Dynamic SQL to script out the "SET part in UPDATE" for the 26 columns
But you have to ignore the computed columns, identity columns, non-nullable colums, etc. and I don't think its worth the effort for updating 26 columns
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 27, 2012 at 12:47 am
One option you can consider is to use Dynamic SQL to script out the "SET part in UPDATE" for the 26 columns
But you have to ignore the computed columns, identity columns, non-nullable colums, etc. and I don't think its worth the effort for updating 26 columns
I often use Excel to help build these long repetitive queries.
You could do all of the updates in one big hit though - no need for all the WHERE clauses.
update table
set col1 = IsNull(Col1,''),
set col2 = IsNull(Col2,''),
etc
This has the disadvantage of rewriting all non-NULL values to disk, but saves you running 26 discrete queries.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 27, 2012 at 12:53 am
Phil Parkin (7/27/2012)
I often use Excel to help build these long repetitive queries.
Yes. Excel is another good option.
I use a combination of both, sometimes Excel and sometimes Dynamic SQL.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 28, 2012 at 3:21 pm
select '['+column_name+']' ColumnName from INFORMATION_SCHEMA.columns where IS_NULLABLE = 'yes' and table_name ='Person'
-- copy and paste the results for this column in below
ColumnName
[Title]
[MiddleName]
[Suffix]
[AdditionalContactInfo]
[Demographics]
-- use the copy and paste columns from above in below and add the set command
update [Person].[Person]
set [Title] = isnull([title], ' '),
[MiddleName] =isnull([middlename], ' '),
[Suffix] = isnull([suffix], ' '),
[AdditionalContactInfo] = isnull([AdditionalContactInfo], ' ') ,
[Demographics] = isnull([Demographics], ' ')
-- here is the count of records
-- (19972 row(s) affected)
-- and finally you have your results in nick of time
SELECT *
FROM [AdventureWorks2012].[Person].[Person]
where firstname = 'dylan' and lastname = 'miller'
Cheers,
John Esraelo
July 28, 2012 at 3:23 pm
I am not sure actually 2 "set" commands in one update work... but see my reply posted here.. one SET is sufficient..
I could be wrong but I do recall having issues with multiple sets command in one update.
Cheers,
John Esraelo
July 28, 2012 at 11:09 pm
John Esraelo-498130 (7/28/2012)
select '['+column_name+']' ColumnName from INFORMATION_SCHEMA.columns where IS_NULLABLE = 'yes' and table_name ='Person'
-- copy and paste the results for this column in below
ColumnName
[Title]
[MiddleName]
[Suffix]
[AdditionalContactInfo]
[Demographics]
-- use the copy and paste columns from above in below and add the set command
update [Person].[Person]
set [Title] = isnull([title], ' '),
[MiddleName] =isnull([middlename], ' '),
[Suffix] = isnull([suffix], ' '),
[AdditionalContactInfo] = isnull([AdditionalContactInfo], ' ') ,
[Demographics] = isnull([Demographics], ' ')
-- here is the count of records
-- (19972 row(s) affected)
-- and finally you have your results in nick of time
SELECT *
FROM [AdventureWorks2012].[Person].[Person]
where firstname = 'dylan' and lastname = 'miller'
Just a bit of a sidebar. If you don't have a WHERE clause in the UPDATE statement, you end up updating all of the rows. That will affect every index you have and fire any triggers you may have for every row. You might want to rebuild your indexes after the update and disable the triggers before the update.
You also need to carefully consider if this will break any code.
Of course, if blanks are wanted and nulls are not, you'll need to update the table so that the columns have a NOT NULL constraint added to them. Again, that could break code so be careful.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 30, 2012 at 10:59 am
This is very true what just got mentioned.
I did not get to the detail but yes you will have to do all that and watch the angles.
And, once you modify the data, then you would want that NOT NULL in there after discussing with the programming staff.. and as a mater of fact, if this is not an in-house application and DB then you need to be careful on the EULA as well.
happy computing..
Cheers,
John Esraelo
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply