Unanswered: unique constraint error (works in Oracle and SQL Server)
I have a problem with an update statement with MySQL (max-nt 4.01).
This statement works very well in Oracle and SQL Server.
update page set ord=ord+1 where ord>=2 and parent=0
the unique key:
parent and ord
table: (a part of it) type InnoDB
id parent ord
1 0 1
2 0 3
3 0 2
4 0 4
1062: duplicate entry '0-3' for key 2
the error message changes of course depending on the contents of the table. key 2 is the unique key and '0-3' are the parent and the ord that have to be unique.
This error appears only when the table has more rows. I guess for the small example above it may actually work. But if I insert more rows and (most important) the statement has more operations to do (the ord is smaller) I will get the error.
There is nothing wrong with the statement or the table because it works in Oracle and SQL Server.
Is there something that I sholud set to make this statement work. Maybe change the table type to another transactional one?
Maybe another MySQL version?