Postgresql, Updating table row for each row in another table

I’m doing a project at school in java/postgresql where you can buy tickets to different concerts and I’m stuck at a place where I want to write a code to be able to refund all money if a concert is canceled.

I want to remove tickets from my users and refund them with the price they bought them at.

My sold tickets table looks like this:

My users’ table looks like this, where pesetas is the currency of the user:

I wrote this, but it only returns the pesetas from the first ticket at the users.

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;

When I do:

SELECT * FROM tic;

It returns

Here you see that userid=5 bought tickets to consertid=2 two different times, one time he bought 5 tickets at 425 each and another time he bought to the same consertid at the sale, 3 tickets at 200 each. My current script above only gives the user back the money for the first time he bought(5*425 Pesetas) and I need to give back for all tickets.

I have also been trying like this, but same result, it just update one row.:

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;

Thanks for any help! 🙂

Answer

You cannot update the same row twice in an UPDATE statement, like the documentation says:

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.

So you should group the prices per user:

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;

Or, with a subselect:

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;

The second variant might be faster on PostgreSQL before v12.