一道SQL考题的思考
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://cloud.tencent.com/developer/article/1551925
最近参加了一次考试，有道题，考察的是个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从理解上，前两种更为常见，多了解两种，作为知识补充。另外，以上的测试，都在数据量很小的情况下，所以没性能问题，如果在实际中用，就需要考虑如何高效执行，因此，能不能满足功能要求，和能不能在真实环境使用，其实是两个问题，其实很重要，但时，往往容易忽略。
腾讯云开发者
