Renumber the keys of MySQL table

renumber-primary-keys.sql

-- Let's say you had a table fruit

CREATE TABLE IF NOT EXISTS `fruit` (
  `id` int(11) NOT NULL auto_increment,
  `blah` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
);

-- Then you populate it, and delete some rows:
-- id, blah
-- 1, apple
-- 4, orange
-- 5, banana

-- Add a new column
ALTER TABLE  `fruit` ADD  `new_key` INT NOT NULL FIRST;

-- Move over the existing keys into the new key
UPDATE `fruit` set new_key = id;

-- Make alterations to the new_key by tightening up:
UPDATE `fruit` set new_key = new_key - 2 where new_key > 1;

-- Now you have:
-- new_key, id, blah
-- 1, 1, apple
-- 2, 4, orange
-- 3, 5, banana

-- Once you have it like you like it, do:
ALTER TABLE `fruit` DROP  `id`;
ALTER TABLE  `fruit` ADD PRIMARY KEY (  `new_key` );
ALTER TABLE  `fruit` CHANGE  `new_key`  `id` INT( 11 ) NOT NULL AUTO_INCREMENT

-- then:
-- id, blah
-- 1, apple
-- 2, orange
-- 3, banana