List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:September 9 1999 2:34pm
Subject:Re: problem with auto_increment while deleting rows
View as plain text  
Deniz Demir wrote:
> 
> Hi,
> 
> I have a table having a auto_increment row. I want to make table's rest
> rows' numbers automaticaly change after a row in the middle of table has
> 
> been deleted. Is it possible?
> or
> I have written a perl CGI to delete rows from table, how can I add this
> task to it also?
> 
> Regards,
> Deniz...

Hi Deniz

No, not automatically,  but you can use an Update to do what you want:
UPDATE 
	auto_table 
SET 
	auto_inc_col = (auto_inc_col -1) 
WHERE 
	auto_inc_col > deleted_row_id
	;

But you must be absolutely sure, that there are no references in other tables to this 
AUTO_INCREMENT column.

If you use 3.23.xx then you have to correct the actual AUTO_INCREMENT value.
This is necessary, because since 3.23.0 the actual AUTO_INCREMENT value is stored in the
table and not longer evaluated through:
MAX( auto_inc_col ) + 1 

Tschau
Christian

PS: Sorry for the late answer, I was really busy.

Thread
problem with auto_increment while deleting rowsDeniz Demir25 Aug
  • Re: problem with auto_increment while deleting rowsChristian Mack9 Sep