aggregate in update problem - SQL Server Forums

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

Transact-SQL (2000)

aggregate in update problem

Author

Topic

omicron777
Starting Member

9 Posts

Posted - 2009-02-26 : 03:54:37

I'm trying to update table 1 poss field from
SUM((table1.wh1 + table1.wh2 + table1.wh3 + table1.wh4) and deduct
table 2 fields (table2.rsv + table2.open) but I get undesirable result on my code.
Instead of calculating it row by row if table 1 cardno is equal to table 2 cardno, my code calculates all table1 and table2 records and put it in every table 1 poss field.

I know I am missing something in my code below.

Need your help to clear this out. Thanks!

table 1

cardno wh1 wh2 wh3 wh4 poss
a123 2.00 2.00 1.00 1.00
a270 10.50 7.25 3.10 1.00
b111 3.00 3.10 1.00 2.00
b358 11.00 8.00 2.00 7.00
c123 3.50 3.50 1.00 3.00

table 2

cardno rsv open
b111 1.00 2.00
a123 1.15 3.15
a270 10.00 1.00
b358 0.00 1.15
c123 7.50 3.00
d112 1.00 2.00

update table1 set poss =
(select SUM((table1.wh1 + table1.wh2 + table1.wh3 + table1.wh4) - (table2.rsv + table2.open))
FROM table2 INNER JOIN table1 ON table1.cardno = table2.cardno)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-26 : 04:17:46

[code]UPDATE t1
SET t1.Poss = COALESCE(t1.wh1, 0) + COALESCE(t1.wh2, 0) + COALESCE(t1.wh3, 0) + COALESCE(t1.wh4, 0) - COALESCE(t2.Data, 0)
FROM Table1 AS t1
LEFT JOIN (
SELECT CardNo,
SUM(COALESCE(rsv, 0) + COALESCE([open], 0)) AS Data
FROM Table2
GROUP BY CardNo
) AS t2 ON t2.CardNo = t1.CardNo[/code]

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-26 : 04:17:56

try this


declare @table1 table(cardno varchar(32),wh1 decimal(18,2),wh2 decimal(18,2), wh3 decimal(18,2), wh4 decimal(18,2), poss decimal(18,2))
insert into @table1 select 
'a123', 2.00, 2.00, 1.00, 1.00, null union all select
'a270', 10.50, 7.25, 3.10, 1.00 ,null union all select
'b111', 3.00, 3.10, 1.00, 2.00,null union all select
'b358', 11.00, 8.00, 2.00, 7.00,null union all select
'c123', 3.50, 3.50, 1.00, 3.00,null 

declare @table2 table(cardno varchar(32),rsv decimal(18,2),openl decimal(18,2))
insert into @table2 select   
'b111', 1.00,2.00 union all select
'a123', 1.15, 3.15  union all select
'a270', 10.00, 1.00 union all select
'b358', 0.00, 1.15 union all select
'c123', 7.50, 3.00 union all select
'd112', 1.00, 2.00

update t
set poss = (wh1+wh2+wh3+wh4)-(rsv+openl)
from
@table1 t
inner join
@table2 tt on tt.cardno = t.cardno

select * from @table1

omicron777
Starting Member

9 Posts

Posted - 2009-02-26 : 04:24:17

Alright!!! That was a quick response.

I apply your codes and got it.

Thanks guys!!!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-26 : 04:36:13

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources