Mysql: Swap data for different rows

Question

Suppose a table fruits that looks like this:

------------------------------------------
| id |    name    |   color   | calories |
------------------------------------------
| 1  | apple      | red       | 20       |
| 2  | orange     | orange    | 10       |
| 3  | grapes     | green     | 5        |
| 4  | bananas    | yellow    | 15       |
| 5  | plum       | purple    | 25       |
------------------------------------------

How can I swap the values of a row, with another, leaving the id number intact?

Example:

SWAP ROW WITH ID "5" WITH ROW WITH ID "2"

Result:

------------------------------------------
| id |    name    |   color   | calories |
------------------------------------------
| 1  | apple      | red       | 20       |
| 2  | plum       | purple    | 25       |
| 3  | grapes     | green     | 5        |
| 4  | bananas    | yellow    | 15       |
| 5  | orange     | orange    | 10       |
------------------------------------------

Note that all the values are intact except for the id. I need to do this with a really large list of values, so I need a one-liner, or at most, something that doesn't require the creation of temporary tables, and things like that.

Note: id is unique

Thank you

Answer 1

You could use a join inequality to line up the rows you want to swap:

update fruit a
 inner join fruit b on a.id <> b.id
   set a.color = b.color,
       a.name = b.name,
       a.calories = b.calories
 where a.id in (2,5) and b.id in (2,5)

http://sqlfiddle.com/#!18/27318a/5

Answer 2

Since ID is unique, it is difficult to just swap the IDs, it's easier to swap the column contents. A query like this might be what you need:

UPDATE
  yourtable t1 INNER JOIN yourtable t2
  ON (t1.id, t2.id) IN ((1,5),(5,1))
SET
  t1.color = t2.color,
  t1.name = t2.name,
  t1.calories = t2.calories

Please see fiddle here.