At 15:28 +0100 1/9/03, Csongor Fagyal wrote:
Hi,
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;
Regards, - Cs.
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
