环境 mysql5.0表:testid 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
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
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;
set a.address=b.phone ,a.phone=b.addressmysql> 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: 0mysql> 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>