根据另一列的值更新列

Question

如何更新结构如下的表：

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 so on

为实现这一结果：

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 so on

我在想如何混合

SELECT DISTINCT parent_id FROM cats AS t;

使用

CREATE SEQUENCE dpos;
UPDATE cats t1 SET position = nextval('dpos') WHERE t.parent_id = t1.parent_id;
DROP SEQUENCE dpos;

虽然我对postgres没有真正的经验，也不知道如何使用某种FOREACH。我很感谢你的帮助

Answer 1

您可以使用row_number()获得增量数字。问题是如何将其分配给特定的行。下面是使用join的一种方法

update cats
    set position = c2.newpos
    from (select c2.*, c2.ctid as c_ctid,
                 row_number() over (partition by c2.parent_id order by NULL) as seqnum
          from cats c2
         ) c2
    where cats.parent_id = c2.parent_id and cats.ctid = c2.c_ctid;

Answer 2

使用row_number函数

select parent_id, 
row_number() over (partition by parent_id order by parent_id) as position_id from table

Answer 3

试试这个：

    UPDATE table_name set table_name.dataID = v_table_name.rn
FROM  
(
    SELECT row_number() over (partition by your_primaryKey order by your_primaryKey) AS rn, id
    FROM table_name
) AS v_table_name
WHERE v_table_name.your_primaryKey = v_table_name.your_primaryKey;