mysql 一个表两列的值交换

环境 mysql5.0

表：test

id name address age phone
1 张三 13245222 22 北京
2 李四 15555555 22 广州


意思就是要使address和phone的值呼唤，结果变为：

id name address age phone
1 张三 北京 22 13245222
2 李四 广州 22 15555555


----------------------------------------------------------------------------
我试过这句，不过不行
update test set address=phone ,phone=address

UPDATE tt a1 INNER JOIN ( 
SELECT *,@a:=a.address AS newaa,@b:=a.`phone` AS newt FROM tt a) b 
ON a1.id=b.id
SET a1.address=newt,a1.`phone`=newaa;

SQL code

update test a inner join test b on a.id=b.id
set a.address=b.phone ,a.phone=b.address

SQL code

mysql> select * from test1
+------+-------+-------+
| sbid | Ename | type  |
+------+-------+-------+
| JS.1 | 甲1   | 厂家2 |
| JS.2 | 甲2   | 厂家1 |
| JS.3 | 甲3   | 厂家1 |
| JS.4 | 甲4   | 厂家2 |
| JS.5 | 乙1   | 厂家2 |
| HN.3 | 乙3   | 厂家1 |
| HN.4 | 乙8   | 厂家1 |
+------+-------+-------+
7 rows in set (0.05 sec)

mysql> update test1 a ,test1 b
    -> set a.Ename=b.type ,a.type=b.Ename
    -> where a.sbid=b.sbid;
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> select * from test1;
+------+-------+------+
| sbid | Ename | type |
+------+-------+------+
| JS.1 | 厂家2 | 甲1  |
| JS.2 | 厂家1 | 甲2  |
| JS.3 | 厂家1 | 甲3  |
| JS.4 | 厂家2 | 甲4  |
| JS.5 | 厂家2 | 乙1  |
| HN.3 | 厂家1 | 乙3  |
| HN.4 | 厂家1 | 乙8  |
+------+-------+------+
7 rows in set (0.00 sec)

mysql>

为何不直接互换两个列名呢？
哈哈

不错