时间戳

首页 > 解决方案 > Postgresql，为另一个表中的每一行更新表行

sql - Postgresql，为另一个表中的每一行更新表行

问题描述

我在 java/postgresql 的学校里做一个项目，在那里你可以购买不同音乐会的门票，我被困在一个地方，我想编写一个代码，以便在音乐会被取消的情况下退还所有款项。

我想从我的用户那里删除门票，并以他们购买的价格退还他们。

我的售票表如下所示： 售票

我的用户表如下所示，其中比塞塔是用户的货币： 用户

我写了这个，但它只返回用户第一张票的比塞塔。

WITH tic AS (
SELECT userid, price, ticketamount
FROM piljetter_system.soldtickets tic
WHERE consertid = 2  // is example number
)
UPDATE piljetter_system.users usr SET pesetas = pesetas + (tic.price * tic.ticketamount)
FROM tic
WHERE usr.userid = tic.userid;

当我做：

SELECT * FROM tic;

它返回 结果

在这里，您可以看到 userid=5 两次购买了 consertid=2 的门票，一次他以 425 的价格购买了 5 张门票，另一次他以 200 的价格购买了同一个 consertid 的门票，3 张门票。我上面的当前脚本仅将用户第一次购买的钱（5 * 425 比塞塔）退还给用户，我需要退还所有门票。

我也一直在尝试这样，但结果相同，它只是更新了一行。：

UPDATE piljetter_system.users allusr
SET pesetas = (tic.price * tic.ticketamount) + allusr.pesetas
FROM piljetter_system.soldtickets tic , piljetter_system.users usr
WHERE tic.userid = allusr.userid
AND tic.consertid=2;

谢谢你的帮助！:)

标签： sqlpostgresql

解决方案

您不能在UPDATE语句中两次更新同一行，如文档所述：

使用时，FROM您应确保连接最多为要修改的每一行生成一个输出行。换句话说，目标行不应连接到来自其他表的多个行。如果是这样，那么只有一个连接行将用于更新目标行，但将使用哪一个是不容易预测的。

因此，您应该对每个用户的价格进行分组：

WITH tic AS (
   SELECT userid,
          sum(price * ticketamount) AS total_price
   FROM piljetter_system.soldtickets tic
   WHERE consertid = 2
   GROUP BY userid
)
UPDATE piljetter_system.users usr
   SET pesetas = pesetas + tic.total_price
FROM tic
WHERE usr.userid = tic.userid;

或者，使用子选择：

UPDATE piljetter_system.users usr
   SET pesetas = pesetas + tic.total_price
FROM (SELECT userid,
             sum(price * ticketamount) AS total_price
      FROM piljetter_system.soldtickets tic
      WHERE consertid = 2
      GROUP BY userid) AS tic
WHERE usr.userid = tic.userid;

第二个变体在 v12 之前的 PostgreSQL 上可能更快。

推荐阅读