Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2002
    Location
    San Jose, California
    Posts
    16

    Post How to change a tables next autoincrement...

    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...
    -magz

  2. #2
    Join Date
    Feb 2002
    Posts
    48

    *

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

    top!

  3. #3
    Join Date
    Jan 2002
    Location
    Kuwait
    Posts
    679
    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:

    Code:
    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 Alhashemi
    PHP, Apache, C, Python, Perl, SQL
    18 related BrainBench certificates

  4. #4
    Join Date
    Jan 2002
    Location
    Kuwait
    Posts
    679
    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
    Ahmad Alhashemi
    PHP, Apache, C, Python, Perl, SQL
    18 related BrainBench certificates

  5. #5
    Join Date
    Jul 2001
    Location
    Coventry, England
    Posts
    130
    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

  6. #6
    Join Date
    Aug 2002
    Location
    San Jose, California
    Posts
    16

    Talking

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •