我已经读过MySql和MySql中两行的Switch id编号：将数据交换为不同的行，但我不明白。假设一张表的颜色如下：
------------------------------------------
| id | box | pouch | color | value |
-----------------------------------------
| 1 | 1 | 1 | red | 30 |
| 2 | 1 | 1 | blue | 20 |
| 3 | 1 | 1 | green | 10 |
| 4 | 1 | 1 | yellow | 40 |
| 5 | 1 | 1 | purple | 20 |
| 6 | 1 | 1 | black | 50 |
| 7 | 1 | 2 | red | 30 |
| 8 | 1 | 2 | blue | 20 |
| 9 | 1 | 2 | green | 10 |
| 10 | 1 | 2 | yellow | 40 |
| 11 | 1 | 2 | purple | 20 |
| 12 | 1 | 2 | black | 50 |
| 13 | 2 | 1 | red | 35 |
| 14 | 2 | 1 | blue | 25 |
| 15 | 2 | 1 | green | 15 |
| 16 | 2 | 1 | yellow | 45 |
| 17 | 2 | 1 | purple | 25 |
| 18 | 2 | 1 | black | 55 |
| 19 | 2 | 2 | red | 35 |
| 20 | 2 | 2 | blue | 25 |
| 21 | 2 | 2 | green | 15 |
| 22 | 2 | 2 | yellow | 45 |
| 23 | 2 | 2 | purple | 25 |
| 24 | 2 | 2 | black | 55 |
------------------------------------------
如何将一行的"值"与另一行交换，同时保持"id"、"box"、"pocket"answers"color"不变？
注意事项：1.我需要用box='2'交换box='1'的值2.行数为动态
示例：
SWAP ROWS OF VALUE WITH BOX='1' WITH ROWS OF VALUE WITH BOX='2'
结果：
------------------------------------------
| id | box | pouch | color | value |
-----------------------------------------
| 1 | 1 | 1 | red | 35 |
| 2 | 1 | 1 | blue | 25 |
| 3 | 1 | 1 | green | 15 |
| 4 | 1 | 1 | yellow | 45 |
| 5 | 1 | 1 | purple | 25 |
| 6 | 1 | 1 | black | 55 |
| 7 | 1 | 2 | red | 35 |
| 8 | 1 | 2 | blue | 25 |
| 9 | 1 | 2 | green | 15 |
| 10 | 1 | 2 | yellow | 45 |
| 11 | 1 | 2 | purple | 25 |
| 12 | 1 | 2 | black | 55 |
| 13 | 2 | 1 | red | 30 |
| 14 | 2 | 1 | blue | 20 |
| 15 | 2 | 1 | green | 10 |
| 16 | 2 | 1 | yellow | 40 |
| 17 | 2 | 1 | purple | 20 |
| 18 | 2 | 1 | black | 50 |
| 19 | 2 | 2 | red | 30 |
| 20 | 2 | 2 | blue | 20 |
| 21 | 2 | 2 | green | 10 |
| 22 | 2 | 2 | yellow | 40 |
| 23 | 2 | 2 | purple | 20 |
| 24 | 2 | 2 | black | 50 |
------------------------------------------
有人有什么想法吗？感谢
创建一个包含要交换的所有值的表。
create table tmp_t as select * from t;
然后更新您的表格两次：
update t t1
inner join tmp_t t2 on t1.color = t2.color
and t1.pouch = t2.pouch
and t1.box = 1 and t2.box = 2
set t1.value = t2.value;
update t t1
inner join tmp_t t2 on t1.color = t2.color
and t1.pouch = t2.pouch
and t1.box = 2 and t2.box = 1
set t1.value = t2.value;
就是这样。看它在sqlfiddle中的现场工作。
要进行交换，请尝试将其中一个重置为临时值。您需要这样做，否则第二次更新将影响第一次更新。
UPDATE mytable SET box = -1 WHERE box = 1; -- Set box 1 to a temporary value
UPDATE mytable SET box = 1 WHERE box = 2; -- Set box 2 to box 1
UPDATE mytable SET box = 2 WHERE box = -1; -- Set box 1 to box 2
我认为不可能有一个负的箱号。如果允许使用-1，请使用其他内容，例如null。
为了额外的安全性，请将以上内容封装在事务中，这样，如果出现任何问题，您就可以回滚。