Question 243126 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL UPDATE with JOIN failing

update  loan  l  
inner join (
        select LoanId, sum(Amount) as Amount 
        from    loanpayment  
        where   LoanId = pLoanId  
        )  as  lp on l.Id = lp.LoanId 

set  l.CurBalance = OriginalAmount + TermMonths *(OriginalAmount * MonthlyRate/100) 
                        - coalesce(lp.Amount,0) 
where   l.Id = pLoanId   ;

The CurBalance is not being updated when I change the TermMonths. Am I missing something ?

Thanks. I am on mysql 7 and 8.

Tags:

2

Answers


  1. 0

    You can try running a simplified version of the query to see if it updates the CurBalance correctly. For example, try updating CurBalance to a fixed value without involving the TermMonths or other calculations.

    Login or Signup to reply.
  2. 0

    I think P.Salmon is right.
    In the subquery try to had a GROUP BY LoanId.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search