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?

Improve this question
3
  • 1
    Please consider reading these suggestions. Commented Apr 6, 2021 at 19:42
  • Following is the desired outcome Where the values for uuid column are taken from? Commented Apr 6, 2021 at 19:43
  • @Akina: UUID values are to be generated Commented Apr 6, 2021 at 19:45

2 Answers 2

Reset to default
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)
Improve this answer
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

Improve this answer
2
  • This also seems to work Commented Apr 6, 2021 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. Commented Apr 6, 2021 at 21:02

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.