Bug #37206 bulk increment contiguous unique values fails, constraint evaluated row-by-row

• Submitted: 4 Jun 2008 21:08
• Modified: 4 Jun 2008 21:45
• Reporter: Jack Bates
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.51a
• OS: Any
• CPU Architecture: Any

[4 Jun 2008 21:08] Jack Bates

Description:
I want to bulk increment a column of contiguous unique values:

mysql> UPDATE bar SET foo = foo + 1 WHERE foo >= 2;

Duplicate entry '3' for key 1

I assume this fails because the unique constraint is evaluated row-by-row, instead of after the bulk operation. If there are no duplicate values before this bulk operation, there should be none after.

I gather that according to the SQL language, constraints should be evaluated after a bulk operation.

http://thread.gmane.org/gmane.comp.db.mysql.general/95600

How to repeat:
CREATE TABLE bar (foo INTEGER NOT NULL, UNIQUE KEY (foo));
INSERT INTO bar VALUES (1);
INSERT INTO bar VALUES (2);
INSERT INTO bar VALUES (3);
UPDATE bar SET foo = foo + 1 WHERE foo >= 2;

[4 Jun 2008 21:45] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read about UPDATE syntax at http://dev.mysql.com/doc/refman/5.0/en/update.html