Updating postgres column with sequence of integers for unique pairs values-CodePudding

I have the postgres table user_team which has columns user_pk, team, exercise, assigned_at and I want to auto-populate the order column with sequence of incrementing integers but only for unique pairs of user_pk and team and ordered by assigned_at: so the column which I have is:

|  user_pk    |  team          |  exercise_pk  | assigned_at  |  
|-------------------------------------------------------------|
| 111         | blue           |  "exercise"   | 2022-03-01   |
| 111         | blue           |  "exercise"   | 2022-03-02   |
| 222         | blue           |  "exercise"   | 2022-03-01   |
| 222         | blue           |  "exercise"   | 2022-03-02   |
| 222         | blue           |  "exercise"   | 2022-03-03   |
| 111         | green          |  "exercise"   | 2022-03-01   |
| 111         | green          |  "exercise"   | 2022-03-02   |
| 111         | green          |  "exercise"   | 2022-03-03   |
| 333         | green          |  "exercise"   | 2022-03-01   |
| 333         | green          |  "exercise"   | 2022-03-02   |

and I want to have

|  user_pk    |  team          |  exercise_pk  | assigned_at  | order|
|--------------------------------------------------------------------|
| 111         | blue           |  "exercise"   | 2022-03-01   |1     |
| 111         | blue           |  "exercise"   | 2022-03-02   |2     |
| 222         | blue           |  "exercise"   | 2022-03-01   |1     |
| 222         | blue           |  "exercise"   | 2022-03-02   |2     |
| 222         | blue           |  "exercise"   | 2022-03-03   |3     |
| 111         | green          |  "exercise"   | 2022-03-01   |1     |
| 111         | green          |  "exercise"   | 2022-03-02   |2     |
| 111         | green          |  "exercise"   | 2022-03-03   |3     |
| 333         | green          |  "exercise"   | 2022-03-01   |1     |
| 333         | green          |  "exercise"   | 2022-03-02   |2     |

Is there any way to do that in one query? I tried with DISTINCT user_pk, team and with answer from: Updating postgres column with sequence of integers :

    update bar b
   set id = b2.new_id
   from (select b.*, row_number() over (order by id) as new_id
         from bar
        ) b2;
   where b.pk = b2.pk;

But still cannot figure it out

CodePudding user response：

It is good that you looked for a similar question. But what did you actually try on your issue, you cannot just copy an answer from there and expect it to even relate to what you need. That just provides a format that may be applicable.

What you need is to identify a unique column or a unique set of columns to identify the specific rows, from there use row_number to assign the desired value for each unique value/set, finally match the generated/extracted identifier to get the desired result column. In the below the CTE used columns user_pk, team, assigned_at to identify specific rows and the targeted new column value. The main part then matched those column for update. (see demo)

with ord_num( user_pk, team, assigned_at, order_seq)  as 
     ( select user_pk, team, assigned_at
            , row_number() over (partition by  user_pk, team 
                                     order by  user_pk, team, assigned_at
                                )
         from user_team 
      )
update user_team ut
   set order_seq = 
       (select order_seq 
          from ord_num os 
         where (ut.user_pk, ut.team, ut.assigned_at) = 
               (os.user_pk, os.team, os.assigned_at)
       ) ;

NOTE: It is extremely poor practice to user order as a column name. It is both a Postgres and SQL Standard reserved word. While you can get away with it, the best outcome is just confusion, at worst processed fail, by doing the incorrect thing and not providing any message. Resulting in complete data corruption. Also applies to any reserved word.