mysql更两列互换新

---------有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