January 12, 2011 at 8:28 pm
I have set of columns (more than 10) and need to update them to 0 if it is null.
for eg: update table set column1=0 where column1 is null
update table set column2=0 where column2 is null
............
............
............
update table set column10=0 where column10 is null
Is there a better way to write in a single statement instead of 10 statements like the one above for 10 different columns?
January 12, 2011 at 10:23 pm
update MyTable
set
column1 = case when column1 is null then 0 else column1 end,
column2 = case when column2 is null then 0 else column2 end,
... and so on ...
column10 = case when column10 is null then 0 else column10 end
where
column1 is null or
column2 is null or
... and so on ...
column10 is null
January 13, 2011 at 1:30 am
facticatech (1/12/2011)
Is there a better way to write in a single statement instead of 10 statements like the one above for 10 different columns?
"Better" is always subjective , and it does depend.
In your case, you could have a 10million row table where 3 rows match the condition "Where ColumnX is null", if you had a usable index on those columns then your original multi statement method would be "better".
However , i would imagine that here it is not the case here and a single scan would be preferable, though i would use ISNULL the effect is the same .
Just a little food for thought 🙂
January 13, 2011 at 2:05 am
Better in terms of performance only. The table has no indexes and is mainly used for writing and less selectivity.
January 13, 2011 at 2:18 am
Many indexes can slow down the update queries and the insert queries is this the reason that no indexes on the table
January 13, 2011 at 2:49 am
You can also write this query it to tell you what are the missing indexes
This will work only on db witch people working and apps are sending queries to this db
select * from sys.dm_db_missing_index_details
January 13, 2011 at 3:51 am
facticatech (1/12/2011)
I have set of columns (more than 10) and need to update them to 0 if it is null.for eg: update table set column1=0 where column1 is null
update table set column2=0 where column2 is null
............
............
............
update table set column10=0 where column10 is null
Is there a better way to write in a single statement instead of 10 statements like the one above for 10 different columns?
hopefully this is what you are looking for
Update table
Set col1=isnull(col1,0),
Col2=isnull(col2,0),
Col3=isnull(col3,0),
….
….
Col10=isnull(col10,0)
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply