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.

Answer 2

If the ID is the primary key then you don't want to change it/be able to change it. If this is only for sorting, I would suggest making an "order" column that is an integer and sorting on that. Table schema would be nice to see for this but this is my recommendation so far.

If ID is created automatically when rows are entered then this idea is an even larger problem. However, now I'm just speculating as I don't know your table schema.

Here is some standard advice on primary keys: Do not think of them as anything except a unique identifier.