DROP TABLE IF EXISTS t2; SET @id=0; CREATE TABLE t2 SELECT @id:=@id+1 AS id, dt, observed FROM t ORDER BY dt; ALTER TABLE t2 MODIFY id INT AUTO_INCREMENT PRIMARY KEY; DROP TABLE t; RENAME TABLE t2 TO t;
Last updated 22 May 2009
The steps are: (i) recreate the table, populating a new column from an incrementing user variable, then (ii) alter the table to add auto_increment and primary key properties to the new column. So given table t with columns named `dt` and `observed`...
DROP TABLE IF EXISTS t2; SET @id=0; CREATE TABLE t2 SELECT @id:=@id+1 AS id, dt, observed FROM t ORDER BY dt; ALTER TABLE t2 MODIFY id INT AUTO_INCREMENT PRIMARY KEY; DROP TABLE t; RENAME TABLE t2 TO t; Last updated 22 May 2009 |
|