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 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?