Solved

# isnull in a update clause ..

Posted on 2004-11-02
1,547 Views
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

thanks.

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)
VALUES
('name',4318,'model','name1',0,0,0,0)

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

0

LVL 24

Accepted Solution

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)

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!
0

Author Comment

thanks joe that was very well explained !
0

LVL 24

Expert Comment

You're welcome!
0

## Featured Post

### Suggested Solutions

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 (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. 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…