Hi everybody!

Can anybody explain to me why the following simple UPDATE-Statement
fails with the message "violation of PRIMARY or UNIQUE KEY constraint":

UPDATE MY_TABLE SET IX = IX + 1 (if IX is a unique integer key, and
the table contains a continuous sequence of values for IX)

Obviously, the unique key constraint is temporarily violated during
the update if it isn't executed in descending order. But shouldn't the
constraint be checked only AFTER the execution of the statement?

Supposed the unique key constraint must be satisfied at ANY time
during the execution, is there a way to determine the order in which
the records are updated? (Unfortunately, ORDER BY isn't allowed in
UPDATE-Statements.) Isn't there any way at all to shift a sequence of
unique values with one UPDATE-Statement?

Thanks in advance for any help,
Helmut