UPDATE to assign serial numbers per group

Question

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

Answer 1

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:

• How do I (or can I) SELECT DISTINCT on multiple columns?

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:

• Serial numbers per group of rows for compound key