Mysql:交换不同行的数据 [英] Mysql: Swap data for different rows
本文介绍了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号不变?
How can I swap the values of a row, with another, leaving the id number intact?
示例:
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之外,所有值均完整无缺. 我需要使用非常大的值列表来执行此操作,因此我需要单行代码,或者最多不需要创建临时表之类的东西.
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.
注意:id是唯一的
谢谢
推荐答案
您可以使用连接不等式来排列要交换的行:
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
这篇关于Mysql:交换不同行的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文