Entry

Can I reuse IDīs that were created by a MySQL auto_increment field and later deleted?

Jun 18th, 2003 17:37
Jeremy Luby, Phil Gesche, Nathan Wallace,

Jeremy Luby's comments

it is possible to create a routine that will reset all of the 
auto_incremented values though and reorder them to the lowest possible 
#s

for example:
$new_number = "1";
$result = @mysql_query ('SELECT * from job_table order by job_id');
while ($changes = @mysql_fetch_array($result))
   	{
   		$new_id = $changes['job_id'];
   		$update = @mysql_query("UPDATE job_table SET
                         job_id ='$new_number' WHERE
                         job_id ='$new_id';");
                $new_number = ($new_number + 1);

   	}

In this example you will need to change job_table to the name of the 
table, and job_id to the name of the auto_incremented field that you 
want to refresh.