Generate GUID in MySQL for existing Data?

I had a need to add a guid primary key column in an existing table and populate it with unique GUID's and this update query with inner select worked for me:

UPDATE sri_issued_quiz SET quiz_id=(SELECT uuid());

So simple :-)

I'm not sure if it's the easiest way, but it works. The idea is to create a trigger that does all work for you, then, to execute a query that updates your table, and finally to drop this trigger:

delimiter //create trigger beforeYourTableUpdate  BEFORE UPDATE on YourTableFOR EACH ROWBEGIN  SET new.guid_column := (SELECT UUID());END//

Then execute

UPDATE YourTable set guid_column = (SELECT UUID());

And DROP TRIGGER beforeYourTableUpdate;

UPDATEAnother solution that doesn't use triggers, but requires primary key or unique index :

UPDATE YourTable,INNER JOIN (SELECT unique_col, UUID() as new_id FROM YourTable) new_data ON (new_data.unique_col = YourTable.unique_col)SET guid_column = new_data.new_id

UPDATE once again:It seems that your original query should also work (maybe you don't need WHERE columnID is not null, so all my fancy code is not needed.

The approved solution does create unique IDs but on first glance they look identical, only the first few characters differ.

If you want visibly different keys, try this:

update CityPopCountry set id = (select md5(UUID()));MySQL [imran@lenovo] {world}> select city, id from CityPopCountry limit 10;+------------------------+----------------------------------+| city                   | id                               |+------------------------+----------------------------------+| A Coruña (La Coruña)   | c9f294a986a1a14f0fe68467769feec7 || Aachen                 | d6172223a472bdc5f25871427ba64e46 || Aalborg                | 8d11bc300f203eb9cb7da7cb9204aa8f || Aba                    | 98aeeec8aa81a4064113764864114a99 || Abadan                 | 7aafe6bfe44b338f99021cbd24096302 || Abaetetuba             | 9dd331c21b983c3a68d00ef6e5852bb5 || Abakan                 | e2206290ce91574bc26d0443ef50fc05 || Abbotsford             | 50ca17be25d1d5c2ac6760e179b7fd15 || Abeokuta               | ab026fa6238e2ab7ee0d76a1351f116f || Aberdeen               | d85eef763393862e5fe318ca652eb16d |+------------------------+----------------------------------+

I'm using MySQL Server version: 5.5.40-0+wheezy1 (Debian)