一道SQL考题的思考

bisal

发布于 2019-12-10 11:01:13

39000

代码可运行

文章被收录于专栏：bisal的个人杂货铺bisal的个人杂货铺

运行总次数：0

代码可运行

本文链接：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