Switch id numbers of two rows in MySql

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开发者_StackOverflow社区 ordered it by id i would have

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

---

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.

---

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.

---

I am dying to know why you need it to be permanent. The only things I can think of are so you can alter the apparent sequence of events.

as others stated, IDs are typically primary keys and auto-numbered, so that makes it difficult, but the obvious way to achieve this is to gather all field data from the two items into an array, and UPDATE each record with the VALUES from the other one.

---

You need to SORT BY something. We need to see your table schema.

---

Swap IDs relatively easy to implement with stored procedure and tempopary table:

CREATE PROCEDURE `swapIDs`(aTable varchar(64),aID1 int(11),aID2 int(11))
BEGIN
 drop temporary table if exists swapIDsTable;
 SET @s = concat('create temporary table swapIDsTable like ',aTable); PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
 SET @s = concat('insert into swapIDsTable select * from ',aTable,' where id=',aID1);  PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
 SET @s = concat('delete from ', aTable, ' where id=',aID1); PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
 SET @s = concat('update ', aTable, ' set id=',aID1,' where id=',aID2); PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
 SET @s = concat('update swapIDsTable set id=',aID2,' where id=',aID1); PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
 SET @s = concat('insert into ', aTable,' select * from swapIDsTable where id=',aID2); PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
 drop temporary table if exists swapIDsTable;
END |

But imagine, you've swaped id's which is updating by another user on client (application) side and only clue (for application) to understand which raw to update is ID. That would be nightmare.

So this procedure can be 100% usefull in only single user usage.

Another way is to update (exchange) all columns values between two rows:

UPDATE targetTable tab1, targetTable tab2 SET tab1.<colX>=tab2.<colX>, tab2.<colX>=tab1.<colX>  where tab1.id=<id1> and tab2.id=<id2>;

There is still need to be some sync mechanism.