Question 188064 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres update delta between

There is table in postgres db:

event_stats_table

id: pk int
rank: int
rank_delta: int
event_id: int
user_id: int

Every record saves user position for every event.

I am trying to write a query which would update rank_delta fields for all users in comparing with the previous event_id. If previous event_id is null – write 0.

Current and previous id are known, I do not need to update data between every event, only selected pair.

For example I need to update deltas between event_id=1 and event_id=2 and write data into event_id=2 row and tried following sql:

update event_stats_table
set rank_delta = st1.rank - coalesce(st2.rank, st1.rank_delta)
from event_stats_table st1
    left outer join event_stats_table st2 on st1.user_id = st2.user_id and st2.event_id = 1
    where st1.event_id = 2;

But this event updates both records instead of st1 only. I tried to put st1 in the first line of sql and got syntax errors, because st1 is not known at that moment.

I appreciate any advises on this case or even better solutions which are no doubt exist, thx.

UPD 1 changed grand_prix_id to event_id everywhere

UPD 2 Adding sample datum:
Before:

id rank rank_delta event_id user_id
1 1 0 1 1
2 5 0 2 1

After:

id rank rank_delta event_id user_id
1 1 0 1 1
2 5 -4 2 1
Tags:

2

Answers


  1. 0

    that is something weird about handling such self joins in Postgres, but you can do

    CREATE TABLE event_stats_table(

id int,
points int,
rank int,
rank_delta int,
event_id int,
user_id int,
  grand_prix_id int)


    CREATE TABLE

    INSERT INTO event_stats_table VALUES( 1,1,2,1,1,1,1), ( 1,1,1,1,2,1,2)

    INSERT 0 2

    WITH st2 as (
   SELECT 
user_id as user_id2,
  rank as rank2,
  event_id as event_id2 FROM  event_stats_table)
update event_stats_table  st1
set rank_delta = rank - coalesce(st2.rank2, rank_delta)
from  st2 
  WHERE st1.user_id = st2.user_id2 and st2.event_id2 = 1
    AND  st1.event_id = 2;


    UPDATE 1

    SELECT * FROM event_stats_table
    id points rank rank_delta event_id user_id grand_prix_id
    1 1 2 1 1 1 1
    1 1 1 -1 2 1 2 
    SELECT 2

    fiddle

    Login or Signup to reply.
  2. 0

    Where you’re going wrong is that the table to updated is already available for referencing columns and is effectively joined to the tables listed in the FROM part of the statement. In your case the tables listed in your FROM don’t change their results depending on which row of the table to be updated is being evaluated. See the UPDATE syntax docs for more information.

    One way to address this would be something like:

    update event_stats_table est
set rank_delta = st1.rank - coalesce(st2.rank, st1.rank_delta)
from event_stats_table st1
    left outer join event_stats_table st2 on st1.user_id = st2.user_id and st2.grand_prix_id = 1
    where st1.grand_prix_id = 2 and est.id = st1.id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search