List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 14 1999 11:55am
Subject:Re: Updating (incrementing) primary keys
View as plain text  
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

Thread
Updating (incrementing) primary keysKristian Köhntopp14 May
  • Re: Updating (incrementing) primary keysChristian Mack14 May
  • Re: Updating (incrementing) primary keysKristian Köhntopp14 May
  • Updating (incrementing) primary keysMichael Widenius14 May
  • Re: Updating (incrementing) primary keysSasha Pachev14 May