Update table based on the query returned in SQL Server

Question

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.

please consider, you are violating 3NF by this - and for that you need a plausible cause ( mostly performance issues ) — Najzero
– Najzero, Commented May 2, 2013 at 11:12
@Najzero these are problem from the old system and i want to correct it as the data are repeated. — Dinup Kandel
– Dinup Kandel, Commented May 2, 2013 at 11:15

RichardTheKiwi · Accepted Answer · 2013-05-02 11:23:52Z

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;

Thanks i tried you first query just with little changes 'set loan_balance = a.loan_balance * repeatTimes'

Collectives™ on Stack Overflow

Update table based on the query returned in SQL Server

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related