一般的写法有如下几种:
update test1 set name =(select name from test2 where test1.id=test2.id);
update test1 a,test2 b set a.name=b.name where a.id=b.id;
update test2 set test2.name=(select name from test1 where test1.id=test2.id) where exists (select * from test1 where test1.id=test2.id);
一般来讲这三种写法都没问题, 只要在test1,和test2中name和id字段都是唯一的...
下面我们分别讨论更改表和关联表的情况:
首先,讨论被关联表有重复的情况:
mysql> select * from test1;
+------+------+
| id | name |
+------+------+
| 1 | k1 |
| 2 | k2 |
| 3 | k3 |
| 4 | kkk |
| 4 | k4 |
| 4 | k4 |
+------+------+
6 rows in set (0.00 sec)
mysql> select * from test2;
+------+------+
| id | name |
+------+------+
| 1 | 2222 |
| 2 | 2222 |
| 3 | 2222 |
| 4 | 2222 |
+------+------+
4 rows in set (0.00 sec)
mysql> update test2 set name = (select test1.name from test1 where test1.id=test2.id)
-> ;
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> update test2 a,test1 b set a.name=b.name where a.id=b.id;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from test2;
+------+------+
| id | name |
+------+------+
| 1 | k1 |
| 2 | k2 |
| 3 | k3 |
| 4 | kkk |
+------+------+
4 rows in set (0.00 sec)
接着讨论被更改表有多余值的情况
多余值是相对于更改条件而言的.比如只更改ID1-4的..而存在id=5的类似的情况;
mysql> select * from test2;
+------+---------+
| id | name |
+------+---------+
| 1 | k1 |
| 2 | k2 |
| 3 | k3 |
| 4 | kkk |
| 5 | gaopeng |
+------+---------+
5 rows in set (0.00 sec)
mysql> select * from test1;
+------+------+
| id | name |
+------+------+
| 1 | k1 |
| 2 | k2 |
| 3 | k3 |
+------+------+
3 rows in set (0.00 sec)
mysql> update test2 set test2.name=(select name from test1 where test1.id=test2.id);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 5 Changed: 2 Warnings: 0
mysql> select * from test2;
+------+------+
| id | name |
+------+------+
| 1 | k1 |
| 2 | k2 |
| 3 | k3 |
| 4 | NULL |
| 5 | NULL |
+------+------+
5 rows in set (0.00 sec)
可以看到,如果不加条件的update,会导致多余的数据被更改为null.
mysql> select * from test2;
+------+------+
| id | name |
+------+------+
| 1 | rrr |
| 2 | rrr |
| 3 | rrr |
| 4 | rrr |
| 5 | rrr |
+------+------+
5 rows in set (0.00 sec)
mysql> update test2 set test2.name=(select name from test1 where test1.id=test2.id) where exists (select * from test1 where test1.id=test2.id);
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from test2;
+------+------+
| id | name |
+------+------+
| 1 | k1 |
| 2 | k2 |
| 3 | k3 |
| 4 | rrr |
| 5 | rrr |
+------+------+
5 rows in set (0.00 sec)
所以要注意关联更改时要注意的条件:
1.驱动表不能有重复值.关联表作为参考值,不能有重复,不然取值时不知道取哪一个值
2.被更改表如有多余值时,一定要加条件,不然,没有被关联到的数据会被更改为null;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20892230/viewspace-2127471/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/20892230/viewspace-2127471/