php - Switch id numbers of two rows in MySql

Question

I am trying to switch the id's of two rows in mysql using php - the more i read about it the more confused i get. There seems to be a lot of conflicting information. Has anybody got a definitive answer.

eg in initial state my rows are

1-Peter-22-germany
2-mary-16-iceland
3-tom-29-france
4-michael-34-greece

and then i would like to swap the id's of rows 2 and 3 so that it would look like this

1-Peter-22-germany
3-mary-16-iceland
2-tom-29-france
4-michael-34-greece

so that if i then ordered it by id i would have

1-Peter-22-germany
2-tom-29-france
3-mary-16-iceland
4-michael-34-greece

Answer 1

UPDATE yourtable SET id=IF(id=2, 3, 2) where id in(2,3)

might do the trick, but this is a bad idea - manipulating/reassigning primary key values is never a good idea.

If this fails, then it's because of a duplicate key violation (most likely), and you'd need to temporarily reassign one of the IDs to something completely other unique value so you don't get a conflict while the reassignment is in progress - this would require you to use two queries at least.