MySQL使用带有多个返回行的子查询更新字段值 [英] MySQL Update a field value with subquery with multiple returning rows
问题描述
我有两个表"bank"和"bonds".每个用户都有一个银行记录,但可以有0个,1个或更多的债券记录.
I have two tables "bank" and "bonds". Each user has a bank record but can have 0, 1, or more bonds records.
我想编写一个脚本,以用户可能持有的多个债券的利益来更新银行"表中的现金"字段.利息由债券"表的issuePrice *票息字段计算得出.但是由于用户可能持有多个债券,因此应该对每个债券进行此操作.
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');");
但是它没有给出预期的结果.我对有2个债券的用户进行了尝试,如果两个债券每个都应给500个利息,那么总共1000个,它只会增加500个,就像只有1个债券一样.如果我将一个债券设置为500利息,将另一个债券计算为1000利息,则突然增加475.
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.
推荐答案
可能有必要确保您的UPDATE语句尝试将每个用户的行仅更新一次.子查询是执行此操作的最佳方法,可以最有效地实现为联接表:
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)
(为获得更多优化,应该消除LOWER和ROUND调用,但这是另一种讨论.)
(For more optimization, the LOWER and ROUND calls should probably be eliminated, but that's another discussion.)
这篇关于MySQL使用带有多个返回行的子查询更新字段值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!