CODEWITHSUNDEEP
mysql
marcostT
marcostT

Reputation: 563

How to reorder a primary key?

I have a table of 5700 records. The primary key is an integer. Now I noticed that some values are missing. Like this:

100 data
101 data 
102 data
104 data

103 is missing. How I can update all the rows so that the order becomes correct (104 becomes 103 in my example) in one SQL command?

Upvotes: 5

Views: 8364

Answers (5)

SeveneduS
SeveneduS

Reputation: 49

Another way, without truncating whole table:

-- Make Backup of original table's content
CREATE TABLE `orig_tbl_backup` SELECT * FROM `orig_tbl`;
-- Drop needed column. 
ALTER TABLE `orig_tbl` DROP `id`;
-- Re-create it
ALTER TABLE `orig_tbl` AUTO_INCREMENT = 1;
ALTER TABLE `orig_tbl` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Upvotes: 1

sanmai
sanmai

Reputation: 30881

Not sure about one command, but you can do it in four commands:

CREATE TABLE `temp` SELECT * FROM `orig_tbl`;
TRUNCATE `orig_tbl`;
-- counter doesn't reset in some old versions
ALTER TABLE `orig_tbl` AUTO_INCREMENT = 1; 
-- now we omit primary key column to let it seal the holes
INSERT INTO `orig_tbl` (`col1`, `col2`) SELECT `col1`, `col2` FROM `temp`;

Unless you're doing this to make it easier to select records randomly, you really should rethink your approach.

Upvotes: 7

MLFR2kx
MLFR2kx

Reputation: 1135

Try this:

SET @var:=0;
UPDATE `table` SET `id`=(@var:=@var+1);
ALTER TABLE `table` AUTO_INCREMENT=1;

Upvotes: 24

fvu
fvu

Reputation: 32953

I'd advise against messing with your PKs unless you really have no choice, it can cause breakage all over the place when that id column is referred by other tables. If the PK really tolerates no gaps, maybe the choice of PK was not ideal...

If you really think you should do this (and are sure nothing will break in other tables):

  • create a table with the same structure as the original one, but use type serial for id
  • copy data without id into that copy table - you'll now have a copy of original with thanks to serial a nice gapless id field
  • empty original table (or faster, create an identical copy and drop original)
  • copy data from copy table into original table including id

Upvotes: 1

Pekka
Pekka

Reputation: 449385

There is no point in doing this.

IDs from deleted records are not re-used on purpose - to make sure that references from other tables to previously deleted records don't suddenly point to the wrong record, for example. It is not a good idea to try to change this.

If you want a numbered list that has no holes, create a second int column that you re-order in your client program whenever necessary (i.e. when a record is deleted).

Upvotes: 10

Related Questions