Update with join and... sum() problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update with join and... sum() problem

Author

Topic

Kanati
Starting Member

36 Posts

Posted - 2002-09-05 : 11:24:27

Ok... Say I have two tables...

Table1
----------------------
ACCTNUM TRANSAMT
----------------------
1 5.00
1 -5.00
2 4.00
2 8.00
2 -7.00
3 15.00
3 10.00

Table2
-----------------------
ACCTNUM BALANCE
-----------------------
1 0.00
2 0.00
3 0.00

I want Table two to have:
-----------------------
ACCTNUM BALANCE
-----------------------
1 0.00
2 5.00
3 25.00

The query I am currently using which doesn't work (and I'm pretty sure I know why) is:

update table2
set balance = (select sum(transamt) from table1 where table1.primssn = table2.acctnum)
from table2
join table1 on table1.acctnum = table2.acctnum

obviously it's the subquery not returning the right results (returning the sum for everything where the acctnum fields match) and updating incorrectly. But how would I change it to only return the sums of the single account numbers?

It seem slike I am so close, but not QUITE there... What am I missing?

Thanks.
Kanati

Kanati
Starting Member

36 Posts

Posted - 2002-09-05 : 11:29:50

oops... forgot to change one of the field names......

update table2
set balance = (select sum(transamt) from table1 where table1.acctnum = table2.acctnum)
from table2
join table1 on table1.acctnum = table2.acctnum

The names of the tables and fields have been changed to protect their identities. Any correlation between these fields and actual fields is purely coincidental. :)

Kanati

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-05 : 11:55:02

how about

update table2
set balance = coalesce((select sum(transamt) from table1 where table1.acctnum = table2.acctnum),0)

or

update table2
set balance = (select sum(transamt) from table1 where table1.acctnum = table2.acctnum)
where exists (select * from table1 where table1.acctnum = table2.acctnum)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kanati
Starting Member

36 Posts

Posted - 2002-09-05 : 12:19:24

neither of those seems to work...

The second one appears to return the same results as mine in that it returns the total sum of ALL records where there are matching records in both tables. What I need however is to update table2's acctbal field with the sum of only those records where acctbal matches in both tables. IE: in table 1, acctnum 1 sum = 0.00... acctnum 2 sum = 5.00 and acctnum 3 sum = 25.00.

The first one doesn't appear to update any of my fields for some reason which I can't explain as yet because up til now I didn't know there was such a command as coalesce. :)

Any other ideas? :)

Thanks again.
Kanati

Edited by - Kanati on 09/05/2002 12:21:23

Edited by - Kanati on 09/05/2002 12:22:29

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-05 : 12:27:29

Are you sure you're running the right thing?

create table #a (i int, j int)
create table #b (i int, j int)
insert #a select 1,5
insert #a select 1,-5
insert #a select 2,4
insert #a select 2,8
insert #a select 2,-7
insert #a select 3,15
insert #a select 3,10

insert #b select 1,0
insert #b select 2,0
insert #b select 3,0

update #b
set j = coalesce((select sum(j) from #a where #a.i = #b.i),0)

select * from #b

drop table #a
drop table #b

i j
----------- -----------
1 0
2 5
3 25

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kanati
Starting Member

36 Posts

Posted - 2002-09-05 : 13:13:20

After further testing I do believe that the coalesce statement works. In the real world however the table has well over a million records in #a, and a tens of thousands in #b. That statement is literally killing our server (dual P4 with a gig of memory) and pegging it at 100% cpu usage and taking a significant amount of time (I stopped it at 15 minutes). Is there any way to limit the cpu usage of a process? (I'll ask that in it's own thread as well).

Thanks again!

Kanati

LLatinsky
Starting Member

38 Posts

Posted - 2002-09-05 : 13:38:50

How about:

UPDATE #b
set #b.j = accountSum
FROM #b JOIN
(SELECT i, sum(J) as accountSum
FROM #a group by i) #a
ON #b.i = #a.i

select * from #b

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-05 : 15:37:36

you can loop through the table doing a batch at a time.

declare @id int, @maxid int, @batchsize int
select @id = min(acctnum) from table1
select @maxid = min(acctnum) from table1
select @batchsize = 1000

while @id < @maxid
begin
update table2
set balance = coalesce((select sum(transamt) from table1 where table1.acctnum = table2.acctnum),0)
where table2.acctnum between @id and @id + @batchsize

select @id = @id + @batchsize +1
end

You could put a delay at the end of the loop also.
To allow it to be interrupted put the id's in a table, take the top say 1000 into a temp table, use the temp table as a join in the update then delete from the id table joining to the temp table - you will need a transaction for this if you stop it by killing it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kanati
Starting Member

36 Posts

Posted - 2002-09-05 : 16:53:03

Thanks for the info guys. Helped a lot. Got it working now. :)

Kanati

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources