Update a column to same value for all rows in a group of the group by 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?
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)