List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:May 14 1999 4:04pm
Subject:Updating (incrementing) primary keys
View as plain text  
>>>>> "kk" == kk  <Kristian> writes:

kk> I have a column "L integer", which also is primary key in that
kk> table. I need to increment all values of L over a certain limit
kk> by 2 to make room for an inserted element.

kk> update t set L=L+2 where L>5;

kk> fails with

kk> ERROR 1022: Can't write, duplicate key in table 't'

kk> because Mysql performs the operation in ascending or arbitrary
kk> order. If Mysql performed this operation in decending order or
kk> checked the key contraint only after performing the entire
kk> update, all would be completey legal.

kk> As things are, I have to drop the primary key and recreate it
kk> after the insert. This is slow.

kk> How would I approach this problem more efficiently?

Something like the following should fix this;

update t set L=L+100000 where L>5;
update t set L=L-999997 where L>100000;

(Assuming that L is an integer and that you don't have any L > 100000)

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