一道SQL考题的思考

拼搏现实的明天。 2023-06-19 02:25 39阅读 0赞

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

![format_png][]

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

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

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

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9hMXE2NGljODZQYW5DNHE0bGlhS3ZhdjFFNnB0d3pjWU9rcUl1eXJlZ0tpYjVpYXh2THk3Q1piaWJTVWQ3VGZYOGE1TXdTN0E1TXloN0wxSmJueVYwOUlmZUhnLzY0MD93eF9mbXQ9anBlZw?x-oss-process=image/format,png