Update a column to same value for all rows in a group of the group by

Question

I have the following table:

name	key	uuid
na1	k1	NULL
na2	k2	NULL
na3	k1	NULL

If two or more rows have the same key value then I want to update the uuid value to the same uuid for those rows. If the key is unique then that row should have a unique uuid value.

Following is the desired outcome:

name	key	uuid
na1	k1	8274e89f-b119-4326-814d-4a864bbbe207
na2	k2	9a6c5f68-a3c2-4250-ac31-cc8c86a6440c
na3	k1	8274e89f-b119-4326-814d-4a864bbbe207

Any ideas how to achieve this?

Answer 1

Something like this, may be

with u (key, uuid) as (
select distinct on (key) key, gen_random_uuid() as uuid from test
)
update test t1 set uuid = (select u.uuid 
  from u
  where u.key = t1.key)

Answer 2

WITH cte AS ( SELECT DISTINCT key, GEN_RANDOM_UUID() uuid
              FROM test )
UPDATE test
SET uuid = cte.uuid
FROM cte
WHERE test.key = cte.key;

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=f4f16265f49eba3e8306e1348d39fde1