Enumerate MySQL table
Published by cybso on
This is a post from my original site, which was hosted by the former blog service of the University of Osnabrück. I have moved it to the new site for archiving. Pages linked in this article may no longer work today, and the blog comments under the article no longer exist. Opinions expressed in this article reflect the point of view of the time of publication and do not necessarily reflect my opinion today.
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`;