SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Git-R-Done
    Join Date
    Nov 2001
    Posts
    1,194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Resetting AutoIndex Number?

    I have a table in a database that does not contain any data. I had submitted a bunch of tests which ran the AutoIndex number up to 33 and I'd like to change it back to 1. I tried running SET INSERT_ID=1; and SET ID=1; then added a new row of data and the AutoIndex number went up to 34.

    Can somebody please tell me how I can set it back to 1 using a MySQL command?
    John Saunders

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about

    Code:
    alter table TABLENAME auto_increment = 1;

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,268
    Mentioned
    116 Post(s)
    Tagged
    1 Thread(s)
    Are you deleting all the old code? If so, try using TRUNCATE TableName which I know works in MS SQL, access and Oracle. Don't know about MySQL. You could also just drop the table and rebuild it which would reset your counter.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Delete From TABLENAME (If you are in autocommit)

    and

    Truncate [TABLE] TABLENAME

    both reset the auto increment field, since both rebuild the table in MySQL. But they both reset it to 0, meaning the next insert will be 1. doing the alter table thing I suggested earlier allows you to specify what the starting number will be, although you still can't specify what the increment step will be.

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,268
    Mentioned
    116 Post(s)
    Tagged
    1 Thread(s)
    delete from tablename will reset the auto-increment field in mySQL. That's interesting. Makes sense, just a different approach than everyone else.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Bookmarks

Posting Permissions

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