0

Suppose I have 2k rows with current highest auto_increment value being 2000. Now, if i delete, say, 1000 rows from the top (1001 to 2000), the auto_increment still continues from 2001 instead of 1001. This results in needless high values of auto_increment. Is there a way to even "auto-adjust" this?

I tried something like below but it doesn't work

ALTER TABLE `updatelogs` AUTO_INCREMENT = (select max(update_id) from updatelogs);
user2473779
  • 711
  • 6
  • 18
  • 2
    Possible duplicate of [change auto\_increment within same table using subquery mysql](http://stackoverflow.com/questions/16673269/change-auto-increment-within-same-table-using-subquery-mysql) ... You can't do this, but there is no reason for you to be doing this. – Tim Biegeleisen Oct 26 '15 at 08:27
  • Yes, this seems to be duplicate of that question! As for the reason, this will be required if, say, last 1000 rows of some 2000 rows are deleted... the auto increment will continue from 2001, i want it to continue from 1001! – user2473779 Oct 26 '15 at 08:35
  • My feeling is that you should be letting the MySQL engine handle the auto increment column (keyword: **auto**). – Tim Biegeleisen Oct 26 '15 at 08:36
  • Sounds like an [XY problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) to me. Most probably what you are asking isn't what you need. – vhu Oct 26 '15 at 08:36
  • @vhu, right! Need to edit the question! – user2473779 Oct 26 '15 at 08:43
  • @TimBiegeleisen, that seems to be the solution decided in the other question as well. But, it will result in unnecessary high values of auto_increment...considering MySQL doesn't "auto" arranges them! – user2473779 Oct 26 '15 at 08:43
  • *why* do you need to reset the auto increment? Why does it matter if there are gaps in the primary key? Are you fine with having gaps within the sequence (e.g. you have 2000 rows; rows with IDs 1500-1600 are deleted)? Are you using it for something else than to identify the row, e.g. use IDs order id shown to the user? – vhu Oct 26 '15 at 09:35

1 Answers1

0

You can use the following query to reset the auto increment column after any number of records have been deleted:

ALTER TABLE `updatelogs` AUTO_INCREMENT = 1;

As this useful SO post describes, this will reset the auto increment column to MAX(update_id) + 1.

Community
  • 1
  • 1
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360