Sometimes the most recent entries get deleted from our table. After the rows are deleted, try to insert some rows. We can observe a gap in the number_values of the auto_increment column, which is ok in many cases. However, many times the requirement is to reset the auto_increment to maintain the sequence. Well, there is a solution for the same.
Let us understand through an example. Assume that we have the table sales_data with the below row entries.
We will be deleting the last two rows from the table sales_data and then inserting a new row and finally select * to sales_data. Observe the below queries for the same.
DELETE FROM sales_data WHERE sale_person_id > 4; INSERT INTO sales_data (sale_person_name,commission_percentage,no_products_sold) VALUES("Rani",15,5000); SELECT * FROM sales_data;
As we can see that since two rows with sale_person_id values ‘5’ and ‘6’ got deleted, the newly inserted row has id ‘7’. To avoid this behaviour, let us write another query, as shown below.
ALTER TABLE sales_data AUTO_INCREMENT=1;
Let us again repeat the insert after deleting the rows and view the results output will be as shown in below figure 1.3
As we can see, the sequence is maintained, and the new row had sale_person_id value as ‘5’ instead of ‘7’.
To know all about How to reset auto_increment in MySQL read through RESET AUTO_INCREMENT in MYSQL.
1 thought on “Reset AUTO_INCREMENT after Delete in MySQL”
Thank you. I am stuck as I want to do the same procedure but in python and not MySQL workbench. I am building a simple grocery store management app where I have a table of products ( product_id,name,unit,price) where product_id is auto incremented .
I have written python code to add and delete products.
But when I delete a product and then add new, the product_id sequence is lost.
query = ALTER TABLE ‘table’ AUTO_INCREMENT =1 INSERT INTO ‘table’ (‘column’) VALUES(‘value’)
Below is the error.
ERROR :You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘INSERT INTO
The above mentioned query works well in the MYSQL workbench but not in the python code.