Sample Query
==============================================================
update R
set R.opn_sal=Q.TSales,r.opn_sal_amt=Q.TSalesAmt
from RDeptStock R,(
SELECT D.prd_id, SUM(D.prd_qty) AS TSales, SUM(D.prd_qty*D.CRate) AS TSalesAmt FROM
sale_detail_cost as D WHERE (D.dept_id = @did)
AND (D.sales_date < @xdate)
GROUP BY D.prd_id) Q
where R.prd_id=Q.prd_id
update s
set s.CRate= (r.Opn_rec_amt-r.Opn_sal_amt+r.Cur_rec_amt)/(r.Opn_rec-r.Opn_sal+r.Cur_rec)
from sale_detail_cost s,RDeptStock r
where r.prd_id=s.prd_id and (s.sales_date >= @xdate) and
(s.sales_date <=@edate) and s.dept_id=@did
and r.Opn_rec-r.Opn_sal+r.Cur_rec<>0
==========================
reference:
http://www.sqlusa.com/bestpractices2005/updatewithcorrelatedsubquery/
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60899
http://www.sql-server-performance.com/forum/threads/aggregate-function-in-update-statement.12043/
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79960