MySQL Bugs: #7290: update should check primary keys after all records altered

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

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.

adding version info

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

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.