Enumerate MySQL table

If you have a table "tablename" with a (non-PK) column "number" and you want to enumerate all existing rows starting with 1000 you can use the following SQL statement:



SET @n = 999;
DROP TABLE IF EXISTS `tablename_tmp`; 
CREATE TEMPORARY TABLE `tablename_tmp` (`tmpid` INT PRIMARY KEY, `n` INT);
INSERT INTO `tablename_tmp` SELECT `id`, @n := @n + 1 FROM `tablename`;
UPDATE `tablename` SET `number` = 
    (SELECT `n` FROM `tablename_tmp` WHERE `tmpid` = `id`);
DROP TABLE `tablename_tmp`;

Pages