CODEWITHSUNDEEP
mysqlauto-increment
Frank Seo
Frank Seo

Reputation: 45

Resetting the auto incrementing column

CREATE TABLE employee
(
    eId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    eName1 VARCHAR(30),
    eName2 VARCHAR(30),
    eType VARCHAR(30),
    phoneNumber VARCHAR(12),
    eSalary INT,
    birthday DATE,
    imageFile VARCHAR(100)
);
SELECT*FROM employee; 
ALTER TABLE employee AUTO_INCREMENT =1;

When I perform ALTER TABLE employee AUTO_INCREMENT =1;, it simply doesn't work :( It says 0 rows affected. enter image description here

This is the data in the table.

Upvotes: 1

Views: 67

Answers (2)

ikyuchukov
ikyuchukov

Reputation: 660

ALTER TABLE ... AUTO_INCREMENT = N can only change the auto-increment counter value to a value larger than the current maximum.

In your case , you can set it to 4 or 4+.

Source: https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

Upvotes: 0

Mech
Mech

Reputation: 4015

This will reset your eId, starting from 1:

UPDATE `tbl1`, (
  SELECT @loop := 0
  FROM
   `tbl1`
  ) t
SET `eId` = (@loop := @loop + 1)

If you want to change the starting eId, just modify the 0 in @loop := 0

Upvotes: 2

Related Questions