Auto_Increment After deleted data from the table

Question

How can I reset the auto_increment of a field? I want it to start counting from 1 again after I delete all the data.

score 2 · Answer 1 · answered Mar 22 '18 at 08:31

2

ALTER TABLE tablename AUTO_INCREMENT = 1;

See also the same question on stackoverflow.

answered Mar 22 '18 at 08:31

dbdemon

6,351
4
19
38

Pankaj Kumar · Answer 2 · 2018-03-22T05:23:15.623

1

It will be better to truncate table to start auto_increment from beginning of a auto_increment field:
Truncate table tableName;
as delete table starts auto_increment from last auto_incremented number.

Second option is, if you have to delete all data then you may drop and re-create table to start auto_increment from beginning.

edited Mar 22 '18 at 05:23

answered Mar 22 '18 at 05:10

Pankaj Kumar

409
2
5
14

thanks,.if my data 1,2,3 then i delete num 2 then it still 1,3 in the table. it should num 3 change into num 2. Can you help? – Siti Aisyah Mar 22 '18 at 06:50
It can't be changed as auto_incremented number checks last existing number. You have to do it manually. – Pankaj Kumar Mar 22 '18 at 06:54
@SitiAisyah your question says "*after I delete all the data*" – ypercubeᵀᴹ Mar 22 '18 at 09:47
No, You may update manually update tableName Set columnName =2 where ColumnName =3 – Pankaj Kumar Mar 22 '18 at 09:53

Auto_Increment After deleted data from the table

2 Answers2