Subject Re: [firebird-support] Violation of UNIQUE KEY constraint during UPDATE
Author Ann W. Harrison
Milan Babuskov wrote:
>>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)
>>Isn't there any way at all to shift a sequence of
>>unique values with one UPDATE-Statement?
> With one, no. But you could try with two. Suppose that you have some
> large values (ex. 10000+) that aren't taken:
> update my_table set ix = ix + 10001;
> update my_table set ix = ix - 10000;

I'd go for the procedure - sorting the whole table then updating it row
by row is a nuisance, but the double update is significantly more expensive.

Regards ,