UPdate a value with group - 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 a value with group

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-09-12 : 12:17:30

Trying to update an existing record, but the value I want I need to get from many records in another table.

Two tables:
banktransactions
orderrebatehistory

I want to sum fields in teh orderrebatehistory and use that value in an update statement to update the banktransactions.

Orderrebate history has:
ord_no inv_no ext_rebate
22 25 1.00
22 25 2.00

So in my script I want to add my two records together and use the 3.00 in an update statement.

update banktransactions
set amountdc = amountdc - (sum(orderrebatehistory.ext_rebate)*banktransactions.TermsPercent)

Can I use a group by in an update statement?? or is there a better way to do this?

pommguest99
Starting Member

16 Posts

Posted - 2008-09-12 : 15:04:06

This will work for you.

update banktransactions b
set [TargetColumn] = (select A.Ext_rebate from
(select Ord_no, sum(ext_rebate) as Ext_Rebate from Orderrebatehistory group by ord_no)A
where A.ord_no = b.ord_no)

regards,
Anil.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-13 : 13:30:52

or use join instead of subquery

update  b
set b.[TargetColumn] = A.Ext_Rebate
from banktransactions b
join(select Ord_no, sum(ext_rebate) as Ext_Rebate from Orderrebatehistory group by ord_no)A
where A.ord_no = b.ord_no

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources