hi guys! thaks for explanations and ideas, well the first problem is ok, change to -1 and -2 and after 2 and 1, that was what i'm considering at the first time i see primary key duplicated, and since mysql don't suport defered insert/update/delete, ok let's workaround :)
now the full problem....
think about a list with 'locked' and 'unlocked' positions, and important fields that make that entry information unique...
every position (id column) must be >0, all positions <=0 i'm considering as 'inserted but not completed transition'
|id|locked|other important data|
|1|false|blabla|
|2|false|blabla2|
|3|true|blabla3|
|4|false|blabla4|
|5|false|blabla5|
|6|true|blabla6|
my problem now is: change it 5 to position 2, and reposition the list, position 2 will become position 4, and position 4 will become position 5, position 3 will not change since it's locked='true'
i'm considering something like, change position 5 to -5, change position 4 to 5, change position 2 to 4, change -5 to 2, but how to do this only with sql language? and the second problem is: change position 2 to position 5, position 4 will become position 2, position 5 will become position 4
any other idea about how to do this? i think that's the easiest way, considering that i will need a lock at positions 2,3,4,5 before start updates, right? maybe i could do this with select * from test where id in (2,3,4,5) for update, or lock table
ideas are wellcome