I have the following table:

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:

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

CodePudding user response：

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.

CodePudding user response：

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