2

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?

4
  • 1
    Please consider reading these suggestions.
    – mustaccio
    Apr 6 '21 at 19:42
  • Following is the desired outcome Where the values for uuid column are taken from?
    – Akina
    Apr 6 '21 at 19:43
  • @Akina: UUID values are to be generated
    – NGambit
    Apr 6 '21 at 19:45
  • @mustaccio : thanks for the pointer and the edit
    – NGambit
    Apr 6 '21 at 19:45
3

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

2
  • This also seems to work
    – NGambit
    Apr 6 '21 at 20:06
  • It is functionally equivalent, but the plans will likely be different. This one sorts on the generated UUIDs, which is unnecessary. Since yours probably is a one-time use case that's likely immaterial.
    – mustaccio
    Apr 6 '21 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.