PostgreSQL UPDATE - 带有左连接问题的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4815694/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL UPDATE - query with left join problem
提问by Dmitro
UPDATE user
SET balance = balance + p.amount
FROM payments p WHERE user.id = p.user_id AND p.id IN (36,38,40)
But it adds to the balance, only the value amount of the first payment 1936. Please help me how to fix it, i do not want to make cycle in the code to run a lot of requests.
但是它增加了余额，只有第一笔付款的价值金额 1936。请帮我如何解决它，我不想在代码中循环运行很多请求。
回答by Quassnoi
In a multiple-table UPDATE, each row in the target table is updated only once, even it's returned more than once by the join.
在多表中UPDATE，目标表中的每一行只更新一次，即使它被连接返回多次。
From the docs:
从文档：
When a
FROMclause is present, what essentially happens is that the target table is joined to the tables mentioned in the fromlist, and each output row of the join represents an update operation for the target table. When usingFROMyou should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.
当一个
FROM子句存在时，本质上发生的事情是目标表连接到 fromlist 中提到的表，连接的每个输出行代表目标表的更新操作。使用时，FROM您应该确保连接最多为要修改的每一行生成一个输出行。换句话说，目标行不应连接到其他表中的多行。如果是这样，则只有一个连接行将用于更新目标行，但将使用哪一个是不容易预测的。
Use this instead:
改用这个：
UPDATE user u
SET balance = balance + p.amount
FROM (
SELECT user_id, SUM(amount) AS amount
FROM payment
WHERE id IN (36, 38, 40)
GROUP BY
user_id
) p
WHERE u.id = p.user_id
