>>>>> "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)
Yours,
Monty
