Bug #7290 update should check primary keys after all records altered
Submitted: 14 Dec 2004 17:27 Modified: 13 May 2010 16:04
Reporter: Yuliy Pisetsky Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:4.1.7 OS:Solaris (Solaris)
Assigned to: Assigned Account CPU Architecture:Any
Triage: D5 (Feature request)

[14 Dec 2004 17:27] Yuliy Pisetsky
Description:
I have a table which has auto-incremented keys. I want to bump each of them up 1 index. I try to do a "UPDATE foo SET id=id+1;" It gives error about a duplicate key. Such a linear transformation cannot result in duplicate keys if they were unique before. 

How to repeat:
create an auto-incremented table, add at least two records. execute "UPDATE foo SET id=id-1;UPDATE foo SET id=id+1;"

Suggested fix:
if a pk being changed conflicts with another record, before erroring out, check to see if that other record is affected by the update, if so, handle it and then redo the check.
[14 Dec 2004 17:52] Yuliy Pisetsky
adding version info
[14 Dec 2004 18:19] Heikki Tuuri
Hi!

This is a known deviation of MySQL from SQL standards. I am changing this to a feature request, since this requires quite a bit of work to fix.

Regards,

Heikki
[10 Oct 2005 19:07] Scott Marlowe
Note that the standard work around for this is to do something like:

update table set id=id+somebignumber;
update table set id=id-(somebignumber-1);

This is the same thing I have to do with PostgreSQL and a few other databases.