Question 21230 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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

Tags:

2

Answers


  1. 0 
    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search