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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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)

Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply