Saved for future reference in case original source is removed. Code modified slightly, no improvements, personal tastes.
SET @row = 0;
UPDATE `table` SET `id` = @row:= @row + 1;
ALTER TABLE `table` AUTO_INCREMENT = 1;Used this to reorder rows in a settings table, which doesn’t rely on the auto-increment primary key for uniqueness. I prefer using composite keys in such circumstances, to avoid useless indexes, but the database schema is not mine to redesign.
Note to self for OpenCart’s settings table 😛
SET @row = 0;UPDATE `oc_setting` SET `setting_id` = @row:= @row + 1;
ALTER TABLE `oc_setting` AUTO_INCREMENT = 1;