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?

i have seen an example how its done for postgres but not sure how to convert that to mysql since very new with this. https://dba.stackexchange.com/questions/289376/update-a-column-to-same-value-for-all-rows-in-a-group-of-the-group-by

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;

example of my dbfiddle: https://dbfiddle.uk/DXNMCZmU

yah i had tried just changing that but i seem to be still getting other errors. for example: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM cte WHERE test.k = cte.k' at line 5 — Attiq Ahmed
– Attiq Ahmed, Commented Jan 16, 2023 at 22:40

Bill Karwin · Accepted Answer · 2023-01-16 22:53:47Z

with cte as ( select `key`, uuid() as uuid from test group by `key` ) update test join cte using (`key`) set test.uuid = cte.uuid;

The multi-table UPDATE syntax you were using is for Microsoft SQL Server. MySQL uses different syntax. (Both are vendor-specific extensions to SQL, because ANSI SQL doesn't support multi-table UPDATE.)

Note that key is a reserved keyword, so you must delimit it to use it as an identifer.

SelVazi · Accepted Answer · 2023-01-16 22:59:31Z

For older versions of mysql, you can do it using inner join

UPDATE test t inner join ( SELECT _key, uuid() as uuid FROM test group by _key ) as cte on cte._key = t._key SET t.uuid = cte.uuid

Demo here : https://dbfiddle.uk/VJxJR_IV

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related