On 25/09/2007, Anoo Sivadasan Pillai <aspillai@xxxxxxxxx> wrote: > > > > > Hi, > > On further testing I found the same behaviour in Unique keys too, The following batch can reproduce the behaviour. > > CREATE TABLE master ( m1 INT primary key , m2 int unique ) ; > > INSERT INTO master VALUES ( 1, 1 ) ; > > INSERT INTO master VALUES ( 2, 2) ; > > UPDATE master SET m2 = m2 + 1; > > Isn't this expected behavior? When you update the m2 of the first record, it becomes 2 and violates the unqiue constraint as the second row already has an m2 value of 2. I have missed the thread, but this reeks of an unusual DB design. If your really want the updates to go in reverse order so that keys are not violated, you can do that in your application -- SELECT the keys you wish to update sorted in DESC order by m2, and then foreach of them, just increment the value accordingly. If you wrap this in a transaction, it'll be pretty fast, depending on how many rows you're talking about (I've found great speeds on records up to 50,000 for a live DB). ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
