sql - Updating column based on another column's value -

how update table structured this:

id[pkey] | parent_id  | position 1           1            2           1 3           1 4           1 5           1  6           2 7           2 8           2 9           2  10          3 11          3 12          3  ...and on 

to achieve result:

id[pkey] | parent_id  | position        1           1               1 2           1               2 3           1               3 4           1               4 5           1               5  6           2               1 7           2               2 8           2               3 9           2               4  10          3               1 11          3               2 12          3               3  ...and on 

i thinking somehow mixing

select distinct parent_id cats t; 

with

create sequence dpos; update cats t1 set position = nextval('dpos') t.parent_id = t1.parent_id; drop sequence dpos; 

although im not experienced postgres, , not sure how use kind of foreach. appreciate help

you can incremental number using row_number(). question how assign particular row. here 1 method using join:

update cats     set position = c2.newpos     (select c2.*, c2.ctid c_ctid,                  row_number() on (partition c2.parent_id order null) seqnum           cats c2          ) c2     cats.parent_id = c2.parent_id , cats.ctid = c2.c_ctid;