[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1552
  • Last Modified:

isnull in a update clause ..

dear experts,

Below is a set of SQL.   I need to update the value of col1=0, i tried the isnull function to accomplish this, obviously i am missing something.  help greatly appreciated


set nocount on

create table #temptable2
(name varchar(30), NO int , MODEL varchar(30),  name1 varchar(30),
COL1 int, COL2 int, COL3 int, COL4 int)

create table #temptable1(top_level_equip int, bandwidth varchar(10), num_items int, Cnt int)

--insert into #temptable1 values(4318, 'ds1',2, 9856)  -- I've commented this purposely to test isnull
insert into #temptable1 values(4318, 'ds3',3, 528)
insert into #temptable1 values(4318, 'oc12',1, 68)
insert into #temptable1 values(4318, 'oc3',1, 48)

Select * from #temptable1

  INSERT INTO #temptable2
        (name, NO, MODEL,  name1,
            COL1 , COL2 , COL4, COL3)

update #temptable2 set COL1=(select sum(isnull(cnt,0)) from #temptable1 where bandwidth='ds1'),
COL2=(select cnt from #temptable1 where bandwidth='ds3'),
COL3=(select cnt from #temptable1 where bandwidth='oc12'),
COL4=(select cnt from #temptable1 where bandwidth='oc3')
where no=4318

select * from #temptable2

drop table #temptable1

drop table #temptable2

  • 2
1 Solution
Joe WoodhousePrincipal ConsultantCommented:
Your subquery:

select sum(isnull(cnt,0)) from #temptable1 where bandwidth='ds1'

means "for all rows in #temptable1 that have bandwidth='dsi', if their cnt column is null, consider it zero for the purposes of the sum".

But that only applies to rows returned. If the result set is empty, then there will be no rows at all, so the "isnull" will never be applied. This is easily tested, if you run:

select sum(cnt) from #temptable1 where bandwidth='ds1'

you'll get a NULL. It's this NULL that you want to convert to a zero, because it seems like what you're wanting to say is "get the sum of the cnt column where bandwidth='ds1'. If there are no rows, then consider this sum to be 0". You can do that with:

isnull((select sum(cnt) from #temptable1 where bandwidth='ds1'), 0)

Your final UPDATE becomes:

update #temptable2 set COL1=isnull((select sum(cnt) from #temptable1 where bandwidth='ds1'), 0),
COL2=isnull((select cnt from #temptable1 where bandwidth='ds3'), 0),
COL3=isnull((select cnt from #temptable1 where bandwidth='oc12'), 0),
COL4=isnull((select cnt from #temptable1 where bandwidth='oc3'), 0)
where no=4318

Good luck!
SadagopankAuthor Commented:
thanks joe that was very well explained !
Joe WoodhousePrincipal ConsultantCommented:
You're welcome!

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now