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
> Siemenswall, D-24107 Kiel, Germany, +49 431 386 436 00
> Using PHP3? See our web development library at
> http://phplib.shonline.de/ (GPL)
>
Do you really need that field to be a primary key? How
about just having an index on it?
--
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
