I'm attempting to switch the id's of two rows in mysql using php - the greater i just read about this the greater confused i recieve. There appears to become a large amount of conflicting information. Has anybody got a definitive answer.

eg in initial condition my rows are

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

after which i must swap the id's of rows 2 and three to ensure that it might seem like this

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

to ensure that basically then purchased it by id i'd have

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

When the ID may be the primary key then you won't want to change it outOrhave the ability to change it out. If this sounds like just for sorting, I recommend making an "order" column that's an integer and sorting on that. Table schema could be nice to determine with this but this really is my recommendation to date.

If ID is produced instantly when rows are joined than the idea is definitely an even bigger problem. However, now I am just taking a chance as I'm not sure your table schema.

Here's some standard suggestions about primary secrets: Don't think about them as anything except a distinctive identifier.

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

might have the desired effect, but this can be a bad idea - adjusting/reassigning primary key values isn't advisable.

If the fails, then it is due to a replica key breach (probably), and you'd have to temporarily reassign among the IDs to something completely other unique value so you do not get a conflict as the reassignment is happening - this could need you to use two queries a minimum of.

You have to SORT BY something. We have to call at your table schema.

I'm dying to understand why you really need it to become permanent. The only real a few things i can think about are so that you can affect the apparent sequence of occasions.

as others mentioned, IDs are usually primary secrets and auto-designated, to ensure that causes it to be difficult, however the apparent way to do this would be to gather all area data in the two products into an assortment, increase each record using the VALUES in the other one.