Home > other >  UPDATE to assign serial numbers per group
UPDATE to assign serial numbers per group

Time:04-14

Table users:

id name group_id column to update
1 Paul 1
2 Mike 1
3 Charlie 1
4 Elza 2

Table groups:

id name
1 coolest group
2 random group

Table users after update:

id name group_id column to update
1 Paul 1 3
2 Mike 1 2
3 Charlie 1 1
4 Elza 2 1

Group 1 has 3 users, we order them by name, and assign each an increment. Group 2 has 1 user, we assign only one increment.

I'm trying to update users, per group, with increments on a specific column according to their name order.

So far I tried:

UPDATE users u
SET columntoupdate = g.increment
FROM (
         SELECT ROW_NUMBER() OVER (ORDER BY name) AS increment
         FROM users u2 INNER JOIN groups g2 ON g2.id = u2.group_id
         WHERE u.group_id = g2.id
     ) g

But u.group_id = g2.id gives me an error about not being able to reference it in the subquery.

Fiddle showing the problem

CodePudding user response:

UPDATE users u
SET    columntoupdate = g.increment
FROM  (
   SELECT u2.id
        , row_number() OVER (PARTITION BY u2.group_id ORDER BY u2.name) AS increment
   FROM   users u2
   ) g
WHERE u.id = g.id
-- AND u.columntoupdate IS DISTINCT FROM g.increment  -- ①
;

db<>fiddle here

No need to involve the table group at all.
You need to PARTITION BY group_id for serial number per group.
And join on the PK column.

① Add this WHERE clause to suppress empty updates (for repeated use). See:

Aside:
You are aware that this data structure is not easily sustainable? Names change, users are added and deleted, gap-less numbers per group are expensive to maintain - and typically unnecessary. See:

  • Related