Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update to 0 if NULL Expand / Collapse
Author
Message
Posted Wednesday, January 12, 2011 8:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 4:51 AM
Points: 26, Visits: 143
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?

Post #1046965
Posted Wednesday, January 12, 2011 10:23 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:11 PM
Points: 3,081, Visits: 11,230

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


Post #1046999
Posted Thursday, January 13, 2011 1:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:26 AM
Points: 1,949, Visits: 8,291
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




Clear Sky SQL
My Blog
Kent user group
Post #1047050
Posted Thursday, January 13, 2011 2:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 4:51 AM
Points: 26, Visits: 143
Better in terms of performance only. The table has no indexes and is mainly used for writing and less selectivity.
Post #1047065
Posted Thursday, January 13, 2011 2:18 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 18, 2011 4:52 AM
Points: 54, Visits: 195
Many indexes can slow down the update queries and the insert queries is this the reason that no indexes on the table
Post #1047072
Posted Thursday, January 13, 2011 2:49 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 18, 2011 4:52 AM
Points: 54, Visits: 195
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
Post #1047089
Posted Thursday, January 13, 2011 3:51 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 583, Visits: 963
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 | MCSA SQL Server 2012
Post #1047138
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse