Reputation: 537

Insert new row with id 1

Is there any possibility to delete the entire Data in table and insert new rows with id starts from 1

Delete Command helps to remove the data from table when we trying to insert new row that id will be Last inserted row id+1(if last inserted id is 5 then new id should be 6) but i want to store that id as 1

any suggestions other than truncate command, Thanks in advance

Upvotes: 5

Answers (7)

Vikas

Reputation: 376

Use TRUNCATE TABLE [tableName]

It will reset the auto increment value. From the docs linked above:

If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.

Upvotes: 1

Payel Senapati

Reputation: 1356

As per requirement of OP, answer by @MudassirHasan is the best solution.

But there is another, but slightly lengthy way to do the same.

You can use ALTER MODIFY statement to remove AUTO_INCREMENT attribute from id, and then again use ALTER MODIFY statement to re-add AUTO_INCREMENT attribute to id. This is demonstrated below -

Here the example table my_table has following structure -

DESCRIBE my_table;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| col1  | varchar(40)  | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

I populate the table with some random values as follows -

INSERT INTO my_table (col1) VALUES
    -> ("abc"),
    -> ("def"),
    -> ("ghi");

SELECT * FROM my_table;
+----+------+
| id | col1 |
+----+------+
|  1 | abc  |
|  2 | def  |
|  3 | ghi  |
+----+------+

Now, I delete all records from my_table -

DELETE FROM my_table;

Now, I use ALTER MODIFY statement to remove AUTO_INCREMENT attribute from column id

ALTER TABLE my_table 
    -> MODIFY id INT UNSIGNED NOT NULL;

Again, I use ALTER MODIFY statement to re-add AUTO_INCREMENT attribute to column id

ALTER TABLE my_table
    -> MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT;

Now, I add a sample value "abc" to column col1 -

INSERT INTO my_table (col1) VALUES
    -> ("abc");

SELECT * FROM my_table;
+----+------+
| id | col1 |
+----+------+
|  1 | abc  |
+----+------+

The newly inserted row starts with id = 1

Upvotes: 0

zkanoca

Reputation: 9918

As @mhasan answers

ALTER TABLE `tablename` AUTO_INCREMENT = 1

is a way to do this.

Another approach is to drop id column and recreate it.

ALTER TABLE `tablename` DROP `id`;
ALTER TABLE `tablename` AUTO_INCREMENT = 1;
ALTER TABLE `tablename` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

But if your table has relations with other tables your will is not a good solution.

Upvotes: -1

Mudassir Hasan

Reputation: 28741

After deleteing all records do

ALTER TABLE tablename AUTO_INCREMENT = 1

Note

From MySQL Docs : Link

You cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

Upvotes: 8