CODEWITHSUNDEEP
php
user1950655
user1950655

Reputation: 1

How to set auto increment value after deleting a middle row

I have a Table

id  paper_title
1   General English1
2   General English2
3   General English3
4   General English4
5   General English5
6   General English6
7   General English7
.
.
.
.
100 General English100.

Suppose If i delete row 4 and row 5, is it possible to update the id of General English6 And General English7 Set to 4,5 respectively and If i delete a row in middle or start the id should reset continuously like 1 2 3 4 5..................100.Please Help.

Upvotes: 0

Views: 2110

Answers (1)

Oswald
Oswald

Reputation: 31685

Determine the id of the row that you want to delete (let's call that id $foo). Then execute

UPDATE tbl SET id = id-1 WHERE id > $foo

get the highest id and call it $max:

SELECT MAX(id) FROM tbl

Then set the new auto increment value:

ALTER TABLE tbl AUTO_INCREMENT = $max+1

Do this for every row you delete. I sure hope id is not your primary key. And anyways, there is definitely a better solution to your underlying problem. There is usually no need to have the rows in a database table numbered from 1 to whatever without gaps.

Upvotes: 1

Related Questions