[SOLVED] Insert different UUID on each row of a large table

Issue

I have a table with ~80k rows with imported data. Table structure is as follows:

order_line_items  
  - id  
  - order_id  
  - product_id  
  - quantity  
  - price  
  - uuid  

On import, the order_id, product_id, quantity, and price were imported, but the uuid field was left null.

Is there a way, using mysql’s UUID() function, to add a uuid to each row of the table in bulk? I could use a script to cycle through each row and update it but if there is a MySQL solution, that would be fastest.

Solution

Each call to uuid() returns a different, unique value.

So a simple

UPDATE order_line_items SET uuid = uuid();

should assign each uuid field a unique value.

---

Edit March 2022

Note that using this method only a few characters change in the uuids, which make them look identical at a glance, but actually they’re all different.

---

*Edit June 2020*

With @RickJames (see comments) we are trying to comprehend how some people can get the same UUID after running the Update command above (they should be all different).

1. MySQL/MariaDB. The question is tagged mysql ; be sure you are running MySQL or MariaDB, as another DBMS might not render that MySQL behavior for the UUID() on multiple rows

2. Perform the Update as shown here, UUID() is a MySQL function (thus the ())

3. Check the field that receives the Update, it must be large enough to hold 36 chars

See also this related question on DBA SE.

Answered By – Déjà vu

Answer Checked By – Gilberto Lyons (BugsFixing Admin)