Update statement- Help - 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

General SQL Server Forums

New to SQL Server Programming

Update statement- Help

Author

Topic

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-25 : 09:34:09

declare @t table (ctrl_number int,credit_code int, debit_code int, trans_amount int)
insert @t
select 1,38,630,5000 union all
select 1,38,630,6000 union all
select 1,48,640,1000 union all
select 1,48,640,2000 union all
select 1,12,38,9000

declare @r table(ctrl_number int, credit_code int, debit_code int,interest_amt int)
insert @r
select 1,90,530,NULL

Hi, the requirement is to update table @r with the interest amt as Sum of trans_amount from @t where credit_code = 38 and 630 and alo credit_code = 48 and debit_code = 640.

So in the above example interest_amt = (5000 + 6000) + (1000 + 2000).

I'm not sure how to write a single update for this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 09:38:00

UPDATE r
set r.interest_amt = (SELECT SUM(t.trans_amount) FROM @t as t where t.credit_code = 38 and t.debit_code = 630 or t.credit_code = 48 and t.debit_code = 640)
FROM @r AS r

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-25 : 09:38:19

[code]
UPDATE t
SET t.interest_amt=t1.Total_interest
FROM @r t
INNER JOIN (SELECT ctrl_number,SUM(trans_amount) AS Total_interest
FROM @t
WHERE (credit_code = 38 and debit_code = 630)
or (credit_code = 48 and debit_code = 640)
GROUP BY ctrl_number)t1
ON t1.ctrl_number=t.ctrl_number
[/code]

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-25 : 09:46:09

Thanks Visakh and Peso..Both the queries fit the bill...thanks much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-25 : 09:48:11

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources