List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 9 2003 4:07pm
Subject:Re: update auto increment
View as plain text  
At 15:28 +0100 1/9/03, Csongor Fagyal wrote:
>Sorry if this had been asked on this list previously.
>If you had an auto_increment field in a table (let's name it "id"), 
>previously (in 3.23.x, 4.0.x) the following (legal) SQL statement 
>resulted in a "duplicate key" error:
>UPDATE whatevertable SET id = id +1;

It's syntactically legal, but the error makes perfect sense.  If you have id
values of 1 and 2 in the table, updating 1 to 2 cannot work, given
that there is already an id value of 2.

>Will this be fixed in 4.1.x? Can it be fixed? Is there a workaround?

As of MySQL 4.0.0, you can use ORDER BY with UPDATE, which may allow you
to solve your problem by updating the record beginning with the largest
id value and working backward:

UPDATE tbl_name SET id = id + 1 ORDER BY id DESC;

>- Cs.

update auto incrementCsongor Fagyal9 Jan
  • Re: update auto incrementChristian Kohlschütter9 Jan
  • Re: update auto incrementPaul DuBois9 Jan