前言

本篇文章讲解的主要内容是：你有没有经历过一个update把其他列数据清空了、使用merge更新合并记录、删除违反参照完整性的记录、给你五种删除重复数据的写法
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

一、一个update把其他列数据清空了？

如标题，没开玩笑。工作中很容易出现这种情况，博主就遇到过很多同事这样做了。
拿个案例讲解吧！

create table empd as select * from emp;
alter table empd add dname varchar2(20) default 'default';
SQL> select * from empd;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO DNAME
----- ---------- --------- ----- ----------- --------- --------- ------ --------------------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20 default
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30 default
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30 default
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20 default
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30 default
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30 default
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10 default
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20 default
 7839 KING       PRESIDENT       1981-11-17    5000.00               10 default
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30 default
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20 default
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30 default
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20 default
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10 default
 1001 test                       2021-10-9 1                            default

15 rows selected

接下来有个需求：只更新部门(10:ACCOUNTING,20:RESEARCH)的数据。其他未更新的部门名称应该保持为default不变。
大家会不会这么写这个SQL?

update empd
   set dname =
       (select dname
          from dept
         where dept.deptno = empd.deptno
           and dept.deptno in (10, 20));

好，看下这个sql的执行结果：

SQL> update empd
  2     set dname =
  3         (select dname
  4            from dept
  5           where dept.deptno = empd.deptno
  6             and dept.deptno in (10, 20));
  

15 rows updated


SQL> select * from empd;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO DNAME
----- ---------- --------- ----- ----------- --------- --------- ------ --------------------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20 RESEARCH
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30 
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30 
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20 RESEARCH
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30 
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30 
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10 ACCOUNTING
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20 RESEARCH
 7839 KING       PRESIDENT       1981-11-17    5000.00               10 ACCOUNTING
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30 
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20 RESEARCH
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30 
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20 RESEARCH
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10 ACCOUNTING
 1001 test                       2021-10-9 1                            

15 rows selected


SQL> rollback;

可以看到，这个语句是对全表做更新，而不是需求所说的部门(10:ACCOUNTING,20:RESEARCH),而且因为部门(30:SALES)没有匹配到的数据，dname均被更新为NULL值了。
可以想象，在生产环境中，大量的数据被清空或改错是多扯淡的行为（DBA肯定背地问候你）？原因在于该语句中少了必要的过滤条件。
以上UPDATE语句的结果及错误用查询语句描述如下：

select deptno,
       dname as old_dname,
       (select dname
          from dept
         where dept.deptno = empd.deptno
           and dept.deptno in (10, 20)) as new_name,
       case
         when empd.deptno not in (10, 20) then
          '无辜的行'
       end as des
  from empd;
DEPTNO OLD_DNAME            NEW_NAME       DES
------ -------------------- -------------- --------
    20 default              RESEARCH       
    30 default                             无辜的行
    30 default                             无辜的行
    20 default              RESEARCH       
    30 default                             无辜的行
    30 default                             无辜的行
    10 default              ACCOUNTING     
    20 default              RESEARCH       
    10 default              ACCOUNTING     
    30 default                             无辜的行
    20 default              RESEARCH       
    30 default                             无辜的行
    20 default              RESEARCH       
    10 default              ACCOUNTING     
       default                             

15 rows selected

正确的思路是要加上限定条件的！！！

select deptno,
       dname as old_dname,
       (select dname
          from dept
         where dept.deptno = empd.deptno
           and dept.deptno in (10, 20)) as new_name,
       case
         when empd.deptno not in (10, 20) then
          '无辜的行'
       end as des
  from empd
  where exists(
  select dname
          from dept
         where dept.deptno = empd.deptno
           and dept.deptno in (10, 20)
  );
DEPTNO OLD_DNAME            NEW_NAME       DES
------ -------------------- -------------- --------
    20 default              RESEARCH       
    20 default              RESEARCH       
    10 default              ACCOUNTING     
    20 default              RESEARCH       
    10 default              ACCOUNTING     
    20 default              RESEARCH       
    20 default              RESEARCH       
    10 default              ACCOUNTING     

8 rows selected

同样的正确的update语句应该这么来：

SQL> update empd
  2     set dname =
  3         (select dname
  4            from dept
  5           where dept.deptno = empd.deptno
  6             and dept.deptno in (10, 20))
  7    where exists(
  8    select dname
  9            from dept
 10           where dept.deptno = empd.deptno
 11             and dept.deptno in (10, 20)
 12    );
 

8 rows updated


SQL> select * from empd;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO DNAME
----- ---------- --------- ----- ----------- --------- --------- ------ --------------------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20 RESEARCH
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30 default
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30 default
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20 RESEARCH
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30 default
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30 default
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10 ACCOUNTING
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20 RESEARCH
 7839 KING       PRESIDENT       1981-11-17    5000.00               10 ACCOUNTING
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30 default
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20 RESEARCH
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30 default
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20 RESEARCH
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10 ACCOUNTING
 1001 test                       2021-10-9 1                            default

15 rows selected


SQL> rollback;

好了，这样更新就对了，当然了我们还可以通过更新VIEW方式去实现这个需求，将需要被更新的数据范围查出来然后update这个view,或则使用merge方式去实现，我这里给一个merge的更新sql：

merge into empd aa
using (select deptno, dname from dept where dept.deptno in (10, 20)) dd
on (aa.deptno = dd.deptno)
when matched then
  update set aa.dname = dd.dname;

我为什么只给merge的呢，一因为update view的方式类似，二的话，咱们看下update语句和merge语句的执行计划：

 Plan Hash Value  : 1616490401 

--------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                   |         |    9 |   405 |    5 | 00:00:01 |
|   1 |   MERGE                           | EMPD    |      |       |      |          |
|   2 |    VIEW                           |         |      |       |      |          |
| * 3 |     HASH JOIN                     |         |    9 |   441 |    5 | 00:00:01 |
|   4 |      INLIST ITERATOR              |         |      |       |      |          |
|   5 |       TABLE ACCESS BY INDEX ROWID | DEPT    |    2 |    26 |    2 | 00:00:01 |
| * 6 |        INDEX UNIQUE SCAN          | PK_DEPT |    2 |       |    1 | 00:00:01 |
| * 7 |      TABLE ACCESS FULL            | EMPD    |    9 |   324 |    3 | 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("AA"."DEPTNO"="DEPTNO")
* 6 - access("DEPT"."DEPTNO"=10 OR "DEPT"."DEPTNO"=20)
* 7 - filter("AA"."DEPTNO"=10 OR "AA"."DEPTNO"=20)

在回头看下正确的update的执行计划：

 Plan Hash Value  : 430611376 

------------------------------------------------------------------------------------
| Id  | Operation                       | Name    | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                |         |    9 |   351 |   21 | 00:00:01 |
|   1 |   UPDATE                        | EMPD    |      |       |      |          |
|   2 |    NESTED LOOPS                 |         |    9 |   351 |    3 | 00:00:01 |
| * 3 |     TABLE ACCESS FULL           | EMPD    |    9 |   324 |    3 | 00:00:01 |
| * 4 |     INDEX UNIQUE SCAN           | PK_DEPT |    1 |     3 |    0 | 00:00:01 |
| * 5 |    FILTER                       |         |      |       |      |          |
|   6 |     TABLE ACCESS BY INDEX ROWID | DEPT    |    1 |    13 |    1 | 00:00:01 |
| * 7 |      INDEX UNIQUE SCAN          | PK_DEPT |    1 |       |    0 | 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("EMPD"."DEPTNO"=10 OR "EMPD"."DEPTNO"=20)
* 4 - access("DEPT"."DEPTNO"="EMPD"."DEPTNO")
* 4 - filter("DEPT"."DEPTNO"=10 OR "DEPT"."DEPTNO"=20)
* 5 - filter(10=:B1 OR 20=:B1)
* 7 - access("DEPT"."DEPTNO"=:B1)
* 7 - filter("DEPT"."DEPTNO"=10 OR "DEPT"."DEPTNO"=20)

从上面执行计划你能看到，使用merge只读了一次dept表！！！如果是大表的话肯定这个快嘛

二、使用merge更新合并记录

前面介绍了MERGE INTO的好处，那么怎么使用呢？下面用注释及等价改写的方式来介绍

merge into empd aa --只更新这个表
using dept dd  --用来更新aa表的表
on (aa.deptno = dd.deptno)--两个表更新时候的关联条件
--匹配条件aa.deptno = dd.deptno
when matched then--当aa表中存在与dd表对应数据时进行update or delete
  update set aa.dname = dd.dname   --这里update的范围就是(aa.deptno = dd.deptno)的范围减去被删掉的 aa.comm is null的结果
  delete where aa.comm is null --where 在matched中只能出现一次，如果这里出现了那update哪里再出现的话，delete的where 条件这个无效，删除时候，只更新comm is null 的数据
when not matched then--当不满足匹配条件aa.deptno = dd.deptno的数据进行下面操作
  insert
    (empno,deptno,dname)
  values
    (dd.deptno, dd.deptno,dd.dname)where(dd.loc is not null) --新增的时候只更新loc is not null的数据，注意这里是aa表中不存在对应数时候才新增
 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO DNAME
----- ---------- --------- ----- ----------- --------- --------- ------ --------------------
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30 SALES
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30 SALES
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30 SALES
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30 SALES
 1001 test                       2021-10-9 1                            default
   40                                                                40 OPERATIONS

6 rows selected

三、删除违反参照完整性的记录

创建测试用表：

create table empc as select * from emp;
create table deptc as select * from dept;
alter table empc
  add constraint pk_empc primary key (EMPNO);
alter table deptc
  add constraint pk_deptc primary key (deptno);
insert into empc(empno,ename,deptno)values(1221,'zhaoyd',50);

当我们增加如下外键时，会因数据违反完整性而报错：

SQL> alter table empc
  2    add constraint FK_DEPTNO foreign key (DEPTNO)
  3    references deptc (DEPTNO);
ORA-02298: 无法验证 (ZYD.FK_DEPTNO) - 未找到父项关键字

这种提示在处理业务时会经常遇到，是因为子表中的数据(deptno:50)与主表不一致（主表中没有deptno:50)引起的。
这时就要处理违反完整性的数据，要根据情况选择在主表中加入数据，或删除子表中的数据。下面选择删除子表中的数据（注意，删除前后要核对数据后再提交，严格地说，应该要先备份表中的数据，再做删除操作）：

delete from empc where not exists (
select null
from deptc 
where empc.deptno=deptc.deptno
);

删除数据后再次添加外键约束就可以了！

alter table empc
add constraint FK_DEPTNO foreign key (DEPTNO)
references deptc (DEPTNO);

这时候如果再在emp表添加一条dept表没有的部门员工，就会报错了

SQL>  insert into empc(empno,ename,deptno)values(1221,'zhaoyd',50);
ORA-02291: 违反完整约束条件 (ZYD.FK_DEPTNO) - 未找到父项关键字

四、给你删除重复数据的五种写法

先创建测试数据

create table deptf as select * from dept;
insert into deptf  where deptno>10;
commit;

下面几种写法，需要根据你的数据分布情况来挑选着使用。

第一种，自关联删除

delete from dept aa
 where exists (select null
          from dept bb
         where aa.deptno > bb.deptno
           and aa.loc = bb.loc
           and aa.dname = bb.dname);

第二种，通过rowid来删除

delete from dept aa
 where exists (select null
          from dept bb
         where aa.rowid > bb.rowid
           and aa.loc = bb.loc
           and aa.dname = bb.dname
        );

第三种，通过分析函数生成序号+rowid删除

delete from dept
 where rowid in (select rid
                   from (select rowid rid,
                                row_number() over(partition by dname order by deptno) as rn
                           from dept)
                  where rn > 1);

第四种，变种，改写成exists删除

delete from dept
 where exists (select null
          from (select rid
                  from (select rowid rid,
                               row_number() over(partition by dname order by deptno) as rn
                          from dept)
                 where rn > 1) ff
         where ff.rid = dept.rowid);

第五种，变种，改写成not exists删除

delete from dept
 where not exists (select null
          from (select rid
                  from (select rowid rid,
                               row_number() over(partition by dname order by deptno) as rn
                          from dept)
                 where rn = 1) ff
         where ff.rid = dept.rowid);

其他写法自行研究
[]~(￣▽￣)~*🤣🤣🤣🤣🤣🤣🤣🤣🤣🤣🤣🤣🤣🤣🤣🤣

总结

不得不说，update这个一定要注意，更新错了提交了，找DBA恢复，肯定会心里问候你！！！因为我曾经这样问候他们哈哈哈。
删除重复数据这类，面试太常问了！

【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！

前言

一、一个update把其他列数据清空了？

二、使用merge更新合并记录

三、删除违反参照完整性的记录

四、给你删除重复数据的五种写法

总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！

前言

一、一个update把其他列数据清空了？

二、使用merge更新合并记录

三、删除违反参照完整性的记录

四、给你删除重复数据的五种写法

总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景