欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

再论关联子查询更新

程序员文章站 2022-06-01 17:07:04
...

之前使用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行