How to auto increment with 1 after deleting data from a MySQL table?
For this, you can use TRUNCATE TABLE command. Let us first create a table −
mysql> create table DemoTable1796
(
StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
StudentName varchar(20)
);
Query OK, 0 rows affected (0.00 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable1796(StudentName) values('Chris Brown');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1796(StudentName) values('David Miller');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1796(StudentName) values('John Doe');
Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement
mysql> select * from DemoTable1796;
This will produce the following output
+-----------+--------------+
| StudentId | StudentName |
+-----------+--------------+
| 1 | Chris Brown |
| 2 | David Miller |
| 3 | John Doe |
+-----------+--------------+
3 rows in set (0.00 sec)
Here is the query to delete records from the table −
mysql> delete from DemoTable1796;
Query OK, 3 rows affected (0.00 sec)
Now insert a record into the table
mysql> insert into DemoTable1796(StudentName) values('John Doe');
Query OK, 1 row affected (0.00 sec)
mysql> select * from DemoTable1796;
+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 4 | John Doe |
+-----------+-------------+
1 row in set (0.00 sec)Now you can use TRUNCATE TABLE command
mysql> truncate table DemoTable1796;
Query OK, 0 rows affected (0.00 sec)
Now, let us insert some records into the table
mysql> insert into DemoTable1796(StudentName) values('John Doe');
Query OK, 1 row affected (0.00 sec)
mysql> select * from DemoTable1796;
+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 1 | John Doe |
+-----------+-------------+
1 row in set (0.00 sec)
Published on 23-Dec-2019 12:06:23
