Update Statement - 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

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-13 : 13:23:33

I have the following Statement:


begin
update oeordhdr_sql
set extra_10 = tot_sls_amt,
tot_sls_amt = tot_sls_amt-(select A.sumExt_rebate from
(select Ord_no,sum(ext_rebate) as sumExt_Rebate from Orderrebate group by ord_no)A
where A.ord_no = oeordhdr_sql.ord_no)
end

This statement reads an orderrebate table that will have mulitple records in it for an order no. Adds the ext_rebate up and updates the tot_sls_amt field in the oeordhdr. That all works fine.

Right now it updates all records in the oeordhdr. Now I only want it to update records that exist in the orderrebate table. Orderrebate and oeordhdr have the key fields in common:

ord_type, ord_no, cus_no

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-13 : 13:53:47

Got it figured out.

begin
update oeordhdr_sql
set extra_10 = tot_sls_amt,
tot_sls_amt = tot_sls_amt-(select A.sumExt_rebate
from(select Ord_no,sum(ext_rebate)
as sumExt_Rebate from Orderrebate
group by ord_no)A
where A.ord_no = oeordhdr_sql.ord_no)
from orderrebate join oeordhdr_sql on orderrebate.ord_type = oeordhdr_sql.ord_type and
orderrebate.ord_no = oeordhdr_sql.ord_no and orderrebate.cus_no = oeordhdr_sql.cus_no
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources