isnull in a update clause ..

Posted on 2004-11-02
Last Modified: 2009-04-03
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

Question by:Sadagopank
    LVL 24

    Accepted Solution

    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!

    Author Comment

    thanks joe that was very well explained !
    LVL 24

    Expert Comment

    by:Joe Woodhouse
    You're welcome!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This is an issue that we can get adding / removing permissions in the vCSA 6.0. We can also have issues searching for users / groups in the AD (using your identify sources). This is how one of the ways to handle this issues and fix it.
    Synchronize a new Active Directory domain with an existing Office 365 tenant
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now