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?
Try this:
SET @var:=0;
UPDATE `table` SET `id`=(@var:=@var+1);
ALTER TABLE `table` AUTO_INCREMENT=1;
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With