CODEWITHSUNDEEP
mysqlsql
Parthi04
Parthi04

Reputation: 1151

How to reset the auto increment number/column in a MySql table

The item_category_id column is auto increment value in the mysql table.

I need to delete all the values & next insertion should start from 1. If i delete all values & try to insert a new row then it starts with some 30's & 40's.

  item_category_id  item_category_name
    1                     qqq
    25                    ccc
    32                    vvv
    29                    bb
    4                     bbb
    31                    hhh
    34                    mmm
    33                    rrr

Upvotes: 14

Views: 25990

Answers (4)

Nghiệp
Nghiệp

Reputation: 4718

SET  @num := 0;

UPDATE your_table SET id = @num := (@num+1);

ALTER TABLE your_table AUTO_INCREMENT =1;

I think this will do it

Upvotes: 3

ServAce85
ServAce85

Reputation: 1622

If you're using PHPmyAdmin to manage your database, you could simply

  • Select YOUR_TABLE
  • Navigate to More
  • Then, navigate to Operations
  • There you will find an AUTO_INCREMENT field that you can alter:

How to change <code>AUTO_INCREMENT</code> value

Upvotes: 8

Moyed Ansari
Moyed Ansari

Reputation: 8461

Reset the auto increment value for a MySQL table

ALTER TABLE `users` AUTO_INCREMENT = 1;

Upvotes: 5

mprabhat
mprabhat

Reputation: 20323

ALTER TABLE TABLENAME AUTO_INCREMENT = 1

Instead of using Delete if you use Truncate that will remove data and will also reset autoincrement.

TRUNCATE TABLE TABLENAME

Upvotes: 36

Related Questions