Update table based on the query returned in SQL Server

I am stuck with a update query. I have a query as

SELECT s_no, loan_id,repayment_date,principal,loan_balance, count(*) as repeatTimes
FROM loan_repayment_plan_mcg  
GROUP BY s_no, loan_id, repayment_date,principal,loan_balance
HAVING count(*) > 1

It returns this 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 to update loan_balance multiplied by repeatTimes from my above select query based on loan_id and repayment_date which combines together to make a unique row.

Answers


The traditional way, using UPDATE from JOIN

update A
set loan_balance = loan_balance * repeatTimes
from loan_repayment_plan_mcg A
join
(
SELECT s_no, loan_id,repayment_date,principal,loan_balance, count(*) as repeatTimes
FROM loan_repayment_plan_mcg  
GROUP BY s_no, loan_id, repayment_date,principal,loan_balance
HAVING count(*) > 1
) B on A.s_no = B.s_no
   and A.loan_id = B.loan_id
   and A.repayment_date = B.repayment_date
   and A.principal = B.principal
   and A.loan_balance = B.loan_balance;

Using windowing functions and CTE in SQL Server 2008

;with cte as (
  SELECT *,sum(loan_balance) over (
             partition by s_no,loan_id,repayment_date,principal,loan_balance) total_balance
  FROM loan_repayment_plan_mcg
)
update cte
   set loan_balance = total_balance
 where loan_balance != total_balance;

Need Your Help

Java parsing a date like Thursday, May 29, 2008 1:45 PM into a more usable format?

java parsing datetime date

I need to parse dates like Thursday, May 29, 2008 1:45 PM for a current project and don't have much time get it done. I realize I can write some custom parser but that would take a while, I've trie...

java calculate time between two timestamps

java android time timestamp date-arithmetic

I need to calculate the time passed between two dates.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.