MySQL Update a field value with subquery with multiple returning rows

I have two tables "bank" and "bonds". Each user has a bank record but can have 0, 1, or more bonds records.开发者_Go百科

I want to write a script that updates the field "cash" in the "bank" table with the interests of the multiple bonds a user might hold. The interest is calculated by issuePrice * coupon fields of the "bonds" table. But since a user might hold multiple bonds, it should do this for each bond.

At the moment, I tried something like this:

$MySQL->db_Query("UPDATE bonds bo, bank ba SET 
        ba.cash = ROUND(ba.cash + (bo.issuePrice * bo.coupon), 2), 
        ba.earned = ROUND(ba.earned + (bo.issuePrice * bo.coupon), 2) 
    WHERE LOWER(ba.user) = LOWER(bo.holder) AND 
        LOWER(bo.holder) <> LOWER('Bank');");

But it doesn't give the expected outcome. I tried it with a user with 2 bonds, if both bonds should give 500 interest each, so a total of 1000, it only adds 500 like there is only 1 bond. If I set one bonds as 500 interest and the other one with an calculated interest of 1000, it suddenly adds 475.

It's probably worthwhile to ensure that your UPDATE statement is trying to update each user's row exactly once. A subquery is the best way to do this, most efficiently implemented as a joined table:

UPDATE bank 
JOIN (SELECT LOWER(bonds.holder) as user,
    SUM(bonds.issuePrice * bonds.coupon) as total
    FROM bonds
    WHERE LOWER(bonds.holder) != 'bank'
    GROUP BY user
) as increments ON increments.user = LOWER(bank.user)
SET bank.cash = ROUND(bank.cash + increments.total, 2),
    bank.earned = ROUND(bank.earned + increments.total, 2)

(For more optimization, the LOWER and ROUND calls should probably be eliminated, but that's another discussion.)

The most straighforward way is to use sub-selects and update the fields individually...

UPDATE bank ba1
SET ba1.cash = ba1.cash + (ROUND(SELECT SUM(bo.issuePrice * bo.coupon)
               FROM bank ba2 JOIN bonds bo ON bo.user = ba2.user
               WHERE ba2.user = ba1.user), 2)
...