Mysql: обмен данными для разных строк

Предположим, что таблица fruits выглядит следующим образом:

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

Как я могу заменить значения строки, с другой, оставив номер id неповрежденным?

Пример:

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

Результат:

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

Обратите внимание, что все значения остаются нетронутыми, за исключением id. Мне нужно сделать это с действительно большим списком значений, поэтому мне нужен один-лайнер или, самое большее, что-то, что не требует создания временных таблиц, и тому подобное.

Примечание: id уникален

Спасибо

Ответ 1

Вы можете использовать неравенство объединения, чтобы выровнять строки, которые вы хотите обменять:

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/#!2/18b9c/1

Ответ 2

Поскольку идентификатор уникален, трудно просто поменять идентификаторы, проще поменять содержимое столбца. Такой запрос может быть вам нужен:

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

Смотрите здесь скрипку здесь.

Ответ 3

Здесь можно временно сохранить значения без использования таблицы temp или фиктивной строки в таблице фруктов:

SELECT name, color, calories FROM fruit WHERE id = 2 INTO @name, @color, @calories;

UPDATE fruit AS f1, fruit AS f2
SET
 f1.name = f2.name, f2.name = @name,
 f1.color = f2.color, f2.color = @color,
 f1.calories = f2.calories, f2.calories = @calories
WHERE (f1.id, f2.id) = (2, 5);

Здесь другое решение, использующее значение фиктивного идентификатора:

UPDATE fruit SET id = 0 WHERE id = 5;
UPDATE fruit SET id = 5 WHERE id = 2;
UPDATE fruit SET id = 2 WHERE id = 0;

Ответ 4

Если ваши операции основаны на ID, и вы хотите поменять местами целые строки, причудливый способ обмена UNIQUE ID - начать их нумерацию по 1 и использовать 0 как временное значение.

Другим способом выполнения этого является использование столбца без знака и использование назначенного значения (то есть.: -1) для временного. Я бы не рекомендовал последнее, поскольку мы эффективно тратим пространство с помощью этого метода. Подробнее см. http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html.