再论关联子查询更新

之前使用oracle，遇到过2个表进行关联子查询更新的坑，没有加exists从句，导致了不匹配的行设置成null,这个问题在mysql中也是一样的。下面是测试

mysql> select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | dddd |
|    2 | NULL |
|    3 | NULL |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from test2;
+------+-------+
| id   | name2 |
+------+-------+
|    1 | dddd  |
+------+-------+
1 row in set (0.00 sec)

mysql> update test1 set name=(select name2 from test2 where test1.id=test2.id);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from test1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | ffffff |
|    2 | NULL   |
|    3 | NULL   |
+------+--------+
重置数据后
mysql> update test1 set name=(select name2 from test2 where test1.id=test2.id) where exists(select 1 from test2 where test1.id=test2.id);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tes1;
ERROR 1146 (42S02): Table 'test.tes1' doesn't exist
mysql> select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | ffff |
|    2 | bb   |
|    3 | cc   |
+------+------+
3 rows in set (0.01 sec)

主要就是test1中有匹配不上test2的id的记录，这样就会被设置为null,如果都有匹配的记录那么就是ok的，看下面的测试

mysql> insert into test1 values(1,'aa');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test1 values(2,'bb');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test1 values(3,'cc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test2;
Empty set (0.00 sec)

mysql> insert into test2 values(1,'eeee');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test2 values(2,'ffffff');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2 values(3,'cc');
Query OK, 1 row affected (0.01 sec)
mysql> update test1 set name=(select name2 from test2 where test1.id=test2.id);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql> select * from test1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | eeee   |
|    2 | ffffff |
|    3 | cc     |
+------+--------+

看到这种的id=3的记录2个表是一致的，更新了2行，匹配了3行