Kristian Köhntopp wrote:
>
> I have a column "L integer", which also is primary key in that
> table. I need to increment all values of L over a certain limit
> by 2 to make room for an inserted element.
>
> update t set L=L+2 where L>5;
>
> fails with
>
> ERROR 1022: Can't write, duplicate key in table 't'
>
> because Mysql performs the operation in ascending or arbitrary
> order. If Mysql performed this operation in decending order or
> checked the key contraint only after performing the entire
> update, all would be completey legal.
>
> As things are, I have to drop the primary key and recreate it
> after the insert. This is slow.
>
> How would I approach this problem more efficiently?
>
> Kristian
>
> --
> Kristian Köhntopp, NetUSE Kommunikationstechnologie GmbH
Hi Kristian
How about using two UPDATE statements.
The first will move all key values above the current maximum.
Then the second moves them back where you want to have them.
Also be carefull, you have to disable the use of KEYs here.
Tschau
Christian
