一道SQL考题的思考

• 2019 年 12 月 10 日
• 筆記

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文链接：https://blog.csdn.net/bisal/article/details/103379362

最近参加了一次考试，有道题，考察的是个SQL基础，但确实具备迷惑，需求很简单，通过子查询，用一张表的数据，和另一张表进行匹配更新，

直接上实验，构造两张表，

SQL> create table testupdate1 (id number, name varchar2(10));  Table created.    SQL> create table testupdate2 (id number, name varchar2(10));  Table created.    SQL> insert into testupdate1 values(1, 'a');  1 row created.    SQL> insert into testupdate1 values(2, 'b');  1 row created.    SQL> insert into testupdate2 values(1, 'e');  1 row created.    SQL> insert into testupdate2 values(3, 'c');  1 row created.    SQL> insert into testupdate2 values(4, 'd');  1 row created.    SQL> commit;  Commit complete.    SQL> select * from testupdate1;          ID NAME  ---------- ----------           1 a           2 b    SQL> select * from testupdate2;          ID NAME  ---------- ----------           3 c           4 d           1 e

单独执行子查询，根据两张表id字段关联，能找到表2的name是e，

SQL> select testupdate1.name from testupdate1, testupdate2       where testupdate1.id=testupdate2.id;  NAME  ----------  e

根据子查询得到的字段name值，执行update，意图是更新表1的name字段，从实际执行看，两张表匹配的记录值(id=1)得到了更新(name=e)，但是两张表不匹配的记录(id=2)更新成了空，

SQL> update testupdate1       set name=(select name from testupdate2       where testupdate1.id=testupdate2.id);  2 rows updated.    SQL> select * from testupdate1;          ID NAME  ---------- ----------           1 e           2

他的陷阱，就在于子查询外部更新update操作，没限定条件，意思是对表1所有记录都更新，其实从执行返回，"2 rows updated."就可以得到验证，实际表1和表2要更新的应该只是1条记录，这个SQL只当表1和表2的id匹配，此时才可能用表2的name更新表1的name，否则要更新的name，就是空。

既然知道了原因，改造方式，可能有很多种。

方案1，在update层where条件中，限定testupdate1的id要在testupdate2中存在，表示只更新testupdate1和testupdate2匹配id的记录，返回“1 row updated."，可以说明，这个操作正确，

SQL> update testupdate1       set name=(select name from testupdate2       where testupdate1.id=testupdate2.id)       where testupdate1.id in (select id from testupdate2);  1 row updated.    SQL> select * from testupdate1;          ID NAME  ---------- ----------           1 e           2

方案2，在update中用exists，确认只更新testupdate1和testupdate2匹配id的记录，

SQL> update testupdate1       set name=(select name from testupdate2       where testupdate1.id=testupdate2.id)       where exists (select 1 from testupdate2       where testupdate2.id=testupdate1.id);  1 row updated.    SQL> select * from testupdate1;      ID NAME  ---------- ----------       1 e       2 b

方案3，用内联视图，通过关联这两张表为一个视图，更新视图的列，但是直接执行，可能会抛出如下的错误，

SQL> update       (select testupdate1.name t1name, testupdate2.name t2name from testupdate1, testupdate2       where testupdate1.id=testupdate2.id and testupdate1.id=1) t       set t1name=t2name;       *  ERROR at line 1:  ORA-01779: cannot modify a column which maps to a non key-preserved table

问题就是两张表缺少主键，用于唯一定位，创建两张表主键，此时就可以正常执行了，

SQL> alter table testupdate1 add constraint pk_testupdate1 primary key (id);  Table altered.    SQL> alter table testupdate2 add constraint pk_testupdate2 primary key (id);  Table altered.    SQL> update (select testupdate1.name t1name, testupdate2.name t2name from testupdate1, testupdate2 where testupdate1.id=testupdate2.id and testupdate1.id=1) t set t1name=t2name;  1 row updated.    SQL> select * from testupdate1;      ID NAME  ---------- ----------       1 e       2 b

方案4，用merge子句，将testupdate2的每条记录通过on和testupdate1进行比较，匹配的执行update，

SQL> merge into testupdate1 t1       using (select testupdate2.name t2name, testupdate2.id t2id from testupdate2) t2       on (t2.t2id=t1.id)       when matched then       update set t1.name=t2.t2name;  1 row merged.    SQL> select * from testupdate1;      ID NAME  ---------- ----------       1 e       2 b

从理解上，前两种更为常见，多了解两种，作为知识补充。另外，以上的测试，都在数据量很小的情况下，所以没性能问题，如果在实际中用，就需要考虑如何高效执行，因此，能不能满足功能要求，和能不能在真实环境使用，其实是两个问题，其实很重要，但时，往往容易忽略。