How can I set the next AUTO_INCREMENT value?

[AthlonInside]

I have a field with auto_increment (actually the table ID).

If you inserted 100 rows and you delete the last 50 rows, the next increment # is still 101. Can we reset it to 51? How can I do it?

[jatar_k]

$maxid = select max(id) from table

$maxid += 1;

insert into table values ($maxid,'blah','blah');

obviously this code won't come close to working as is but you get the idea. ;)

[AthlonInside]

Jatar, Thanks for you reply. I get your idea. :)

Do you mean there are no way setting the next auto increment # in MySQL itself!

[jatar_k]

don't know, I dont usually worry about it, the auto increment keeps them in line. I dont worry if there are blanks usually.

If I want to be sure of no blanks I use the above method to always get the next id.

[bonanza]

I also don't worry about the gaps.

However, apparently, you can use:

alter table tablename set AUTO_INCREMENT = 100 (or whatever)

I haven't done this myself, but I read somewhere that that only works on an empty table, like if you wanted to start at a certain number.

Try at your own risk!

[DrDoc]

MySQL manual:
[mysql.com...]

[jatar_k]

ah yes, so

ALTER TABLE tbl_name AUTO_INCREMENT = 100

instead of 100 you could use the max(id) +1 to reset it

[AthlonInside]

Ok, I have tested it myself and it works! Although I don't know why the 'Next Autoindex' shown in phpmyadmin is unchanged.?! bug bug?!