Postgres 13

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);
INSERT INTO USERS VALUES (2,'Mike',1);
INSERT INTO USERS VALUES (3,'Charlie',1);
INSERT INTO USERS VALUES (3,'Elza',2);

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

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




1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

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
ERROR:  invalid reference to FROM-clause entry for table "u"
LINE 6:          WHERE u.group_id = g2.id
                       ^
HINT:  There is an entry for table "u", but it cannot be referenced from this part of the query.


select * from users


id
name
group_id
columntoupdate


1
Paul
1
null

2
Mike
1
null

3
Charlie
1
null

3
Elza
2
null