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.
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: