Update multi row with 1 FK key

Question

UPDATE LoanAccount
SET LoanAmount = (
        SELECT ((la.LoanAmount * (DATEDIFF(DAY, la.LoanDate, GETDATE()) * (la.InterestRate / la.LoanTerm)))) + la.LoanAmount
        FROM Customer c, LoanAccount la
        WHERE c.Customer_ID = la.Customer_ID
            AND c.AccountNumber = 213451
        )
    , LoanDate = GETDATE()
WHERE Customer_ID = 2;

I cannot update all LoanAmount for the Customer with ID=2, this is the message

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any solution for this

The sub-query must not return more than one row. (Perhaps you want to make sure Customer_ID=2 in the sub-select too?) — jarlh, Oct 13 '15 at 14:37
ya i have 4 rows of Customer_ID=2 in the LoanAccount table and i want to update all the Amount of that customer , one customer can have many loan account in my project. — Nguyễn Hoàng Lập, Oct 13 '15 at 14:47

score 1 · Answer 1 · edited May 23 '17 at 12:14

It's because following subquery returns more than one row.

SELECT ((la.LoanAmount * (DATEDIFF(DAY, la.LoanDate, GETDATE()) * (la.InterestRate / la.LoanTerm)))) + la.LoanAmount
FROM Customer c, LoanAccount la
WHERE c.Customer_ID = la.Customer_ID
    AND c.AccountNumber = 213451;

This should be correct syntax:

UPDATE LA
SET LA.LoanAmount = ((LA.LoanAmount * (DATEDIFF(DAY, LA.LoanDate, GETDATE()) * (LA.InterestRate / LA.LoanTerm)))) + LA.LoanAmount
    , LA.LoanDate = GETDATE()
-- SELECT *
FROM dbo.LoanAccount AS LA
INNER JOIN dbo.Customer AS C
    ON C.Customer_ID = LA.Customer_ID
WHERE C.Customer_ID = 2
    AND C.AccountNumber = 2;

Also, please avoid using old join syntax as it's deprecated. This question discusses it in depth.

Update multi row with 1 FK key

1 Answers1