CODEWITHSUNDEEP
phpmysql
red23jordan
red23jordan

Reputation: 2891

delete one row in the middle and how to refresh the table id(auto increment) in mysql

For example: 

Row Name
1   John
2   May
3   Marry
4   Tom
5   Peter

Suppose I delete row 2 and row 3, is it possible to update Tom and Peter to row id 2 and 3 respectively and the next insert row to be row id 4?

Upvotes: 4

Views: 1974

Answers (3)

Prasad Rajapaksha
Prasad Rajapaksha

Reputation: 6180

This is just a suggestion. I don't say this is the best solution. Just consider.

You execute your delete query.

DELETE FROM table_name WHERE Row IN (2,3);

Once deleted you make a select query request with PHP and get the data set to an array.

SELECT Row, Name from table_name ORDER BY Row ASC;

Then make a UPDATE execution using a loop.

$index = 1;
foreach($dataset as $item)
{
// Your update query
$sql = "UPDATE table_name SET Row=$index where Name='" . $item['Name'] . "'";
$index++;
}

Before you insert next query you have to get the max value of Row and set +1 value as the Row of the insert query. This is just an idea. Not the complete code.

Upvotes: 0

Elen
Elen

Reputation: 2343

yes, but you need to recreate Row:

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

Upvotes: 2

Levi Botelho
Levi Botelho

Reputation: 25234

No, because think of the problems that this could create. Imagine if you are managing a store inventory and each item has an internal barcode, based on an ID in a database table. You decide never to stock one item again and take it out of the database, and then every ID for every item with an ID greater than the removed item gets decremented by 1... all those barcodes on all those items become useless.

ID numbers in databases aren't generally meant to be used or referenced by people. They are meant to be a static index of a certain item in a database which allows the computer to pull up a given record quickly and reliably. When creating your ID field of your table, make sure you make the datatype large enough to account for these lost IDs when they do occur.

Upvotes: 1

Related Questions