Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : How to change a tables next autoincrement...

[mAgz]

see subject... let's say I have a few rows in a table and have 1 field (id) acting as the primary and index with autoincrement and the default set to 1... what is the query to change the next autoincrement in case i were to allow rows to be deleted from a table or something like that... because if i were to delete a row at the end or in the middle the autoincrement would stay at the exact same number and when i next add a row it will be added in the next autoincrement leaving a blank where the deleted row is...

[MMD]

I never found the answer to this, apparently there is a MySQL query to run but I haven't found it yet :|

top!

[Ahmad]

Insert a row with the ID you want MySQL to start at minus one. Say you want to start at 512, so you insert a row with ID 511. Then delete that row. That's it. Next row will be 512, then 513, .. etc.

Example:

mysql> CREATE TABLE mytest (
    -> testid integer not null auto_increment primary key );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO mytest (testid) VALUES(511);
Query OK, 1 row affected (0.01 sec)

mysql> DELETE FROM mytest WHERE testid = 511;
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO mytest VALUES();
Query OK, 1 row affected (0.01 sec)

mysql> SELECT testid FROM mytest;
+--------+
| testid |
+--------+
|    512 |
+--------+
1 row in set (0.00 sec)

[Ahmad]

Ops. I haven't read your post correctly. Sorry.

As you can see, the problem you are haveing is used as a solution to another problem. Some people won't like it if it gets solved

[benoire]

ALTER TABLE tablename AUTO_INCREMENT = 1

This will reset the next auto_increment to be used to the highest available, so if you had 100 records and deleted the last 50, it would reset from 101 to 51. If you deleted the first 50, however, the auto_increment would stay at 101 and not go to 1

[mAgz]

aite... thx guys... the alter table query is what i was lookin for