专栏首页bisal的个人杂货铺一道SQL考题的思考

一道SQL考题的思考

版权声明:本文为博主原创文章,遵循 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

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 通过dblink使用expdp能不能导出9i库的数据?

    expdp/impdp是10g推出的导入导出工具,9i下是没有该工具的,有人提议用10g的expdp,通过dblink导出9i的数据,乍一听感觉是那么回事,但究...

    bisal
  • 一次夜维SQL的性能优化

    最近单位搬家,从国家会议中心,搬往空气清新的顺义后沙峪,搬迁之前的完结上线中,碰见了一些棘手的问题,有一些值得借鉴的地方。

    bisal
  • MySQL 5.6 rpm安装方法和碰见的问题

    前几天尝试装了MySQL 5.7,《MySQL的rpm和源码两种安装操作》,用了rpm和源码编译两种方法,由于项目需要,这次使用MySQL 5.6版本,rpm安...

    bisal
  • 前端页面之间传递参数

    这里有假如传递一个参数,在另一个页面接收时,只需要js中写入如下代码: localhost:8080/index.jsp?id=1

    微醺
  • .NET 基金会项目介绍-UWP Community Toolkit

    UWP Community Toolkit 是属于 .Net 基金会的一个项目,本文将简要介绍该项目相关的信息。

    newbe36524
  • 机器学习中的统计学——概率分布

    伯努利分布(Bernoulli distribution)是关于布尔变量xϵ{0,1}的概率分布,其连续参数μϵ[0,1]表示变量x=1的概率。其概率分布可以写...

    统计学家
  • Springboot+ajax传输json数组以及单条数据的方法

    Dream城堡
  • 单点登录实现思路及自定义实现方案

    单点登陆概念: single sign on 又称SSO,设计目标就是用户只需要登录一次即可在无需再次登录的情况下访问相关联的其它系统,

    肖哥哥
  • HPP攻击原理介绍和利用

    注意:本文分享给安全从业人员,网站开发人员和运维人员在日常工作中使用和防范恶意攻击,请勿恶意使用下面描述技术进行非法操作。

    WeiyiGeek
  • 译 | 宣布ML.NET 1.2 及模型生成器更新(用于 .NET 的机器学习)

    我们很高兴地宣布ML.NET 1.2 和模型生成器和 CLI 的更新。ML.NET是 .NET 开发人员的开源和跨平台机器学习框架。ML.NET还包括模型生成器...

    Edi Wang

扫码关注云+社区

领取腾讯云代金券