How to update a column with unique serial number depending on two different columns of the same rows in PostgreSQL?

Question

I have a list of records in the below format (example) produced by joining multiple tables from the database and where conditions:

Col1	Col2	Col3
100	200	1
100	201	1
100	202	1
100	203	1
101	204	1
101	205	1
102	206	1
102	207	1

What I would like is to update the values in Col3 in the above example based on the values in Col1 and Col2.

The idea is to loop through Col1 first and then another loop in Col2 and update the values in Col3 starting from 1 and increment by 1 per each iteration over Col2 record. This should be repeated again for the next iteration on Col1.

A sample output expected from the above method is:

Col1	Col2	Col3
100	200	1
100	201	2
100	202	3
100	203	4
101	204	1
101	205	2
102	206	1
102	207	2

The database used is postgres and I am quite new to the functions like cursor in postgres. If anyone have any insight on this to have an efficient way to workout this problem will be great.

Appreciate your help thanks.

Thanks

Answer 1

You can achieve this easily with row_number()over() ranking window function:

Schema and insert statement:

 create table table1(Col1 int,  Col2    int,Col3 int);
 insert into table1 values(100  ,200    ,1);
 insert into table1 values(100  ,201    ,1);
 insert into table1 values(100  ,202    ,1);
 insert into table1 values(100  ,203    ,1);
 insert into table1 values(101  ,204    ,1);
 insert into table1 values(101  ,205    ,1);
 insert into table1 values(102  ,206    ,1);

Update query:

 with cte as(
    select col1,col2,col3, row_number()over (partition by col1 order by col2) rn from table1
 )
 update table1 set col3=cte.rn
 from cte
 where table1.col1=cte.col1 and table1.col2=cte.col2;

In above query row_number()over (partition by col1 order by col2) will generate an unique sequence for each distinct value in col1 starting from 1 and order by col2.

Select query:

 select * from table1;

Output:

col1	col2	col3
100	200	1
100	201	2
100	202	3
100	203	4
101	204	1
101	205	2
102	206	1

db<>fiddle here

Answer 2

You could phrase the update with the help of a correlated count subquery:

UPDATE yourTable t1
SET Col3 = (SELECT COUNT(*) FROM yourTable t2
            WHERE t2.Col1 = t1.Col1 AND t2.Col2 <= t1.Col2);

The actual query you use might be slightly more complicated than above, assuming the output you showed us resulted from a join. However, you should be able to follow the same pattern. Note that my answer also assumes that the Col2 values associated with each Col1 value would always be unique.