Update table based on the query returned in SQL Server -

i stuck update query. have query as

select s_no, loan_id,repayment_date,principal,loan_balance, count(*) repeattimes loan_repayment_plan_mcg   group s_no, loan_id, repayment_date,principal,loan_balance having count(*) > 1 

it returns output:

s_no    loan_id   repayment_date    principal   loan_balance    repeattimes 1         21111   2012-03-13            0.00    5000.00            2 2         21311   2012-04-12            0.00    2000.00            2 3         21111   2012-05-13            500     5000.00            2 4         21111   2012-06-14            0.00    5000.00            3 

i want update loan_balance multiplied repeattimes above select query based on loan_id , repayment_date combines make unique row.

the traditional way, using update join

update set loan_balance = loan_balance * repeattimes loan_repayment_plan_mcg join ( select s_no, loan_id,repayment_date,principal,loan_balance, count(*) repeattimes loan_repayment_plan_mcg   group s_no, loan_id, repayment_date,principal,loan_balance having count(*) > 1 ) b on a.s_no = b.s_no    , a.loan_id = b.loan_id    , a.repayment_date = b.repayment_date    , a.principal = b.principal    , a.loan_balance = b.loan_balance; 

using windowing functions , cte in sql server 2008

;with cte (   select *,sum(loan_balance) on (              partition s_no,loan_id,repayment_date,principal,loan_balance) total_balance   loan_repayment_plan_mcg ) update cte    set loan_balance = total_balance  loan_balance != total_balance;