CODEWITHSUNDEEP
mysqlauto-incrementalter
Abhilash Gupta
Abhilash Gupta

Reputation: 19

How to Reset auto_increment value in MySQL

I am not able to reset the auto_increment value even after making changes after referring to other post

I tried : 

ALTER TABLE tablename AUTO_INCREMENT = 101

ALTER TABLE users AUTO_INCREMENT=1001;

or if you haven't already added an id column, also add it

ALTER TABLE users ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD INDEX (id);

But still not working

Check this :

mysql> ALTER TABLE table2 ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> ADD INDEX (id);
Query OK, 5 rows affected (0.17 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from table2;
+----------------+----+
| name           | id |
+----------------+----+
| Abhilash Gupta |  1 |
| John           |  2 |
| Peter          |  3 |
| Clarke         |  4 |
| Virat          |  5 |
+----------------+----+
5 rows in set (0.00 sec)

mysql> ALTER TABLE table2 AUTO_INCREMENT=101;
Query OK, 5 rows affected (0.25 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from table2;
+----------------+----+
| name           | id |
+----------------+----+
| Abhilash Gupta |  1 |
| John           |  2 |
| Peter          |  3 |
| Clarke         |  4 |
| Virat          |  5 |
+----------------+----+
5 rows in set (0.00 sec)

mysql>

I want the value of id to start from 101. Thanks in advance

Upvotes: 0

Views: 3476

Answers (3)

Amit Agrawal
Amit Agrawal

Reputation: 1

This is how it should be written

cur.execute('''ALTER TABLE tablename AUTO_INCREMENT=0''')

Upvotes: 0

Rhythem Aggarwal
Rhythem Aggarwal

Reputation: 356

Follow this link for reference to AUTO INCREMENT

Now what you are doing is i think correct, but the changes are not reflected because you did not try to enter a new row to the database. Alter command changes the AUTO INCREMENTS value but that will only be reflected in the next insert to the database. It will not affect the data that is already present in the TABLE. Try entering a new row to the DB and check if the ID Value changes.
If not then post the output after entering that row.

Upvotes: 0

Barmar
Barmar

Reputation: 782488

If you want to change the existing IDs to start from 101, use:

UPDATE table2
SET id = id + 100;

The auto_increment setting is used for the ID of the next row to be added, it has no effect on existing rows.

Upvotes: 2

Related Questions