CODEWITHSUNDEEP
mysql
Jury A
Jury A

Reputation: 20102

Auto increment does not change

I'm trying to change the auto increment value to make it starts from a specific number:

ALTER TABLE batchinfo AUTO_INCREMENT = 20000;

But when I query the records, the old numbering (1,2...) still exists, although, the statement seems successful. I'm using MySQL workbench. What could be the problem ?

Upvotes: 2

Views: 2218

Answers (3)

Jocelyn
Jocelyn

Reputation: 11413

Your query sets a new start value for the auto-increment values generated from now on. It will not update the auto-increment values already stored in the table.

Upvotes: 1

cdhowie
cdhowie

Reputation: 169478

Altering AUTO_INCREMENT does not change past generated values, it only sets what the next generated value will be. If you want to change past values then you will need to perform an UPDATE. For example, something like this:

UPDATE batchinfo SET id = id + 19999;

Be careful that you don't have any other tables referencing this column, or their references will be invalidated. (Unless those references are properly noted; only InnoDB tables support foreign keys. And all of those foreign keys will have to be defined with ON UPDATE CASCADE for this command to have the intended effect.)

Upvotes: 5

Nikola K.
Nikola K.

Reputation: 7155

That query doing the change of next AUTO_INCREMENT value. It doesn't change existing values.

If you already have values 1, 2, 3, ... 100, and then change AUTO_INCREMENT to 20000, next entered value will not be 101, but 20000.

Upvotes: 2

Related Questions