Updating Primary auto increment table.

[Jolten]

How can I reset an autoincrement database table to rebuild it's numbers?

Here's the scenario: I have a database table with 118 rows. Due to testing the first 82 rows are gone. So I want to reset the autoincrement field for all rows - 82 so row 83 is numbered 1, 84 = 2, 85 = 3 and so on.

I tried UPDATE autoincrement SET autoincrement = (autoincrement - 82) but that doesn't seem to work. I'm guessing maybe because the autoincrement field is the primary key for the database.

I know I could change these all manually... but can't it be done with one simple query?

Thanks

[Zachery]

Originally Posted by Jolten

How can I reset an autoincrement database table to rebuild it's numbers?

Here's the scenario: I have a database table with 118 rows. Due to testing the first 82 rows are gone. So I want to reset the autoincrement field for all rows - 82 so row 83 is numbered 1, 84 = 2, 85 = 3 and so on.

I tried UPDATE autoincrement SET autoincrement = (autoincrement - 82) but that doesn't seem to work. I'm guessing maybe because the autoincrement field is the primary key for the database.

I know I could change these all manually... but can't it be done with one simple query?

Thanks

I don't think it can be done (or at least thats what I have been lead to believe)

As far as i know, you would need to drop the table make it again, then reimport your data and make sure the data was imported with the proper id's and not their old numbers ect.

[Dean C]

You could take away the auto_increment. Go and change all the values and then change it back?

[Jolten]

Thanks guys. I'm discovering the same thing. they can be edited manually but a query won't function.

[miz]

here a way

hmm you can a temp table with auto increment and just
read from old and inset into new 1
then delete old table and recreat it and do the progrees again

way 2 :

PHP Code:

$bla = select * from table name;
$i=0;
while ($bla2 = $DB_site->fetch_array($bla))
{
i++;
$DB_site->query("Upade table Set id='$i' ");
} 


shoudl work
way 2 is better.

[Jolten]

Thanks miz.. I'll try that.