Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to reorder a primary key?

Tags:

mysql

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?

like image 849
marcostT Avatar asked Nov 27 '22 23:11

marcostT


2 Answers

Try this:

SET @var:=0;
UPDATE `table` SET `id`=(@var:=@var+1);
ALTER TABLE `table` AUTO_INCREMENT=1; 
like image 191
MLFR2kx Avatar answered Dec 05 '22 03:12

MLFR2kx


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).

like image 21
Pekka Avatar answered Dec 05 '22 04:12

Pekka