Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE users (
   id INT,
   name VARCHAR(20),
   group_id INT,
   columntoupdate VARCHAR(10)
);

INSERT INTO users VALUES
  (1,'Paul',1)
, (2,'Mike',1)
, (3,'Charlie',1)
, (3,'Elza',2)
;

CREATE TABLE groups (
   id INT,
   name VARCHAR(20)
);

INSERT INTO groups VALUES
  (1,'coolest group')
, (3,'random group')
;

CREATE TABLE



INSERT 0 4



CREATE TABLE



INSERT 0 2


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  -- ①
;

UPDATE 4


TABLE users;


id
name
group_id
columntoupdate


3
Elza
2
1

3
Charlie
1
1

2
Mike
1
2

1
Paul
1
3



SELECT 4