PostgreSQL 更新 - 带有左连接问题的查询

发布于 2024-10-14 06:19:23 字数 200 浏览 5 评论 0原文

UPDATE user
SET balance = balance + p.amount 
FROM payments p WHERE user.id = p.user_id AND p.id IN (36,38,40)

但它增加到余额中的只是 1936 年第一笔付款的价值金额。请帮助我如何修复它，我不想在代码中循环运行大量请求。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

江南月 2024-10-21 06:19:23

在多表 UPDATE 中，目标表中的每一行仅更新一次，即使连接返回多次。

来自文档：

当存在 FROM 子句时，实质上发生的事情是目标表连接到 fromlist 中提到的表，并且连接的每个输出行代表目标表的更新操作。使用 FROM 时，您应该确保连接为要修改的每一行最多生成一个输出行。换句话说，目标行不应连接到其他表中的多个行。如果是这样，则仅使用连接行之一来更新目标行，但将使用哪一行是不容易预测的。

使用这个代替：

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

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.

From the docs:

When a FROM clause 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 using FROM you 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.

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

回复收藏 0 原文

~没有更多了~