mysql更两列互换新

来源：互联网发布：linux 解压缩文件命令编辑：程序博客网时间：2024/05/21 21:28

---------有where条件的更新，DDL不适用

mysql> select * from t001;
+------+-------+-------+
| id   | name1 | name2 |
+------+-------+-------+
|    1 | a1    | b1    |
|    2 | a2    | b2    |
+------+-------+-------+

mysql> show create table t001;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t001 | CREATE TABLE `t001` (
`id` int(11) DEFAULT NULL,
`name1` varchar(10) DEFAULT NULL,
`name2` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> update t001 set name1=name2,name2=name1;
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from t001;
+------+-------+-------+
| id   | name1 | name2 |
+------+-------+-------+
|    1 | b1    | b1    |
|    2 | b2    | b2    |
+------+-------+-------+
2 rows in set (0.00 sec)

这没有达到我的需求

正确办法是

update t001

set name1=concat(name1,':',name2),

name2=substring_index(name1,':',1),

name1=substring_index(name1,':',-1)

mysql> select * from t001;
+------+-------+-------+
| id   | name1 | name2 |
+------+-------+-------+
|    1 | b2    | a1    |
|    2 | b2    | a2    |
+------+-------+-------+
2 rows in set (0.00 sec)

如果字段类型是int的

update t001

set name1=name1+name2

,name2=name1-name2

,name1=name1-name2