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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SadagopankAuthor Commented:
thanks joe that was very well explained !
Joe WoodhousePrincipal ConsultantCommented:
You're welcome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.