Reputation: 1
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
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