开发者

Oracle表关联更新几种方法小结

目录
  • 1、测试表及数据准备
  • 2、update set column ... where exists
    • 2.1、update set 单列字段
    • 2.2、update set 多列字段
  • 3、使用游标
    • 4、merge into子句
      • 5、oracle 23c/AI 新特性
        • 5.1、关联更新update
        • 5.2、关联删除delete

      1、测试表及数据准备

      create table T_update01(ID int ,infoname varchar2(32),sys_guid varchar2(36));
      create table T_update02(ID int ,infoname varchar2(32),sys_guid varchar2(36));
       
      insert into T_update01
      select 1,N'1_updateName',sys_guid() from dual
      union
      select 2,N'2_updateName',sys_guid() from dual;
      commit;
       
      insert into T_update02
      select 1,N'update_set_exists',sys_guid() from dual;
      insert into T_update02
      select 2,N'update_set_cursor',sys_guid() from dual;
      insert into T_update02
      select 3,N'3_Name',sys_guid() from dual;
      commit;
       
      -- 查询表T_update01、T_update02
      select * from T_update01;
       
              ID INFONAME                       SYS_GUID
      ---------- ------http://www.devze.com------------------------ ------------------------------------
               1 1_updateName                   189F5A1099BF6606E0639C0AA8C0F15E
               2 2_updateName                   189F5A1099C06606E0639C0AA8C0F15E
       
      select * from T_update02;
       
              ID INFONAME                       SYS_GUID
      ---------- ------------------------------ ------------------------------------
               1 update_set_exists              189F5A1099C46606E0639C0AA8C0F15E
               2 update_set_cursor              189F5A1099C56606E0639C0AA8C0F15E
               3 3_Name                         189F5A1099C66606E0639C0AA8C0F15E

      2、update set column ... where exists

      2.1、update set 单列字段

      -- update set 单列字段,更新满足关联条件的所有数据
      update T_update01 T1
      set infoname=(select T2.infoname from T_update02 T2 where T2.ID=T1.ID)
      where exists (select 1 from T_update02 T2 where T2.ID=T1.ID );
       
      -- update set 单列字段 ,更新满足特定条件ID=1的数据
      update T_update01 T1
      set infoname=(select T2.infoname from T_update02 T2 where T2.ID=T1.ID)
      where T1.ID=1;
       
      -- 本次执行更新满足特定条件T_update01表的ID=1
      SCOTT@prod02> select * from T_update01;
       
              ID INFONAME                       SYS_GUID
      ---------- ------------------------------ ------------------------------------
               1 update_set_exists              189F5A1099BF6606E0639C0AA8C0F15E
               2 2_updateName                   189F5A1099C06606E0639C0AA8C0F15E

      2.2、update set 多列字段

      -- T_update01表多插入一行数据
      insert into T_update01
      select 3,N'insert03',sys_guid() from dual;
      commit;
       
      select * from T_update01;
       
              ID INFONAME                       SYS_GUID
      ---------- ------------------------------ ------------------------------------
               1 update_set_exists              189F5A1099BF6606E0639C0AA8C0F15E
               2 2_updateName                   189F5A1099C06606E0639C0AA8C0F15E
               3 insert03                       189F5A1099C76606E0639C0AA8C0F15E
       
      update T_update01 T1 
      set (sys_guid,infoname) = (select T2.sys_guid,T2.infoname from T_update02 T2 where T2.ID=T1.ID)
      where exists (select 1 from T_update02 T2 where T2.ID=T1.ID );
      commit;
      -- 更新后检查,sys_guid,infoname两列的值和T_update02一样了
      select * from T_update01;
       
              ID INFONAME                       SYS_GUID
      ---------- ------------------------------ ------------------------------------
               1 update_set_exists              189F5A1099C46606E0639C0AA8C0F15E
               2 update_set_cursor              189F5A1099C56606E0639C0AA8C0F15E
               3 3_Name                         189F5A1099C66606E0639C0AA8C0F15E
       
      select * from T_update02;
       
              ID INFONAME                       SYS_GUID
      ---------- ------------------------------ ------------------------------------
               1 update_set_exists              189F5A1099C46606E0639C0AA8C0F15E
               2 update_set_cursor              189F5A1099C56606E0639C0AA8C0F15E
               3 3_Name                         189F5A1099C66606E0639C0AA8C0F15E

      3、使用游标

      -- T_update02数据更新一下,方便使用游标更新的结果显示
      update T_upda编程客栈te02 set INFONAME='cursor is select' where id>=2;
      commit;
      select * from T_update02;
       
              ID INFONAME                       SYS_GUID
      ---------- ------------------------------ ------------------------------------
               1 update_set_exists              189F5A1099C46606E0639C0AA8C0F15E
               2 cursor is select               189F5A1099C56606E0639C0AA8C0F15E
               3 cursor is select               189F5A1099C66606E0639C0AA8C0F15E
       
      -- 使用用游标更新T_update01的INFONAME字段,使其和T_update02 where id>=2
      declare
        cursor cur_my_source is select infoname,id from T_update02;
        begin
           for cur_my_target in cur_my_source loop
             update T_update01 set infoname=cur_my_target.infoname where id=cur_my_target.id;
           end loop;
      	 commit;
       end;
       /
       
      -- 检查查询结果
      select * from T_update01;
       
              ID INFONAME                       SYS_GUID
      ---------- ------------------------------ ------------------------------------
               1 update_set_exists              189F5A1099C46606E0639C0AA8C0F15E
               2 cursor is select               189F5A1099C56606E0639C0AA8C0F15E
               3 cursor is select               189F5A1099C66606E0639C0AA8C0F15E

      4、merge into子句

      create table T_merg01(ID int ,infoname varchar2(32),sys_guid varchar2(36));
      create table T_merg02(ID int ,infoname varchar2(32),sys_guid varchar2(36));
       
      insert into T_merg01
      select 1,N'1_Name',sys_guid() from dual
      union
      select 2,N'2_Name',sys_guid() from dual;
      commit;
       
      select * from T_merg01;
              ID INFONAME                       SYS_GUID
      ---------- ------------------------python------ ------------------------------------
               1 1_Name                         189F5A1099BB6606E0639C0AA8C0F15E
               2 2_Name                         189F5A1099BC6606E0639C0AA8C0F15E
       
       
      insert into T_merg02
      select 1,N'merge_into_Name1',sys_guid() from dual;
       
      insert into T_merg02 
      select 3,N'3_Name',sys_guid() from dual;
       
      select * from T_merg02;
       
              ID INFONAME                       SYS_GUID
      ---------- ------------------------------ ------------------------------------
               1 merge_into_Name1               189F5A1099BD6606E0639C0AA8C0F15E
               3 3_Name                         189F5A1099BE6606E0639C0AA8C0F15E
       
       
      merge into T_merg01 T1
      using T_merg02 T2 on (T1.id=T2.id)
      when matched then update set infoname=T2.infoname
      when not matched then insert (ID,infoname,sys_guid) values(T2.ID ,T2.infoname,T2.sys_guid);
       
      commit;
       
      select * from T_merg01;
              ID INFONAME                       SYS_GUID
      ---------- ------------------------------ ------------------------------------
               1 merge_into_Name1               189F5A1099BB6606E0639C0AA8C0F15E
               2 2_Name                         189F5A1099BC6606E0639C0AA8C0F15E
               3 3_Name                         189F5A1099BE6606E0639C0AA8C0F15E
      -- 可以发现T_merg01表的ID=1的INFONAME=merge_into_Name1和T_merg02表ID=1的值一样了
      -- 可以发现T_merg01表多了一行数据是T_merg02表ID=3的这一行数据

      5、Oracle 23c/AI 新特性

      不论是已发版本Oracle23c free还是最终发布的长期支持的Oracle23Ai,表关联更新update和删除delete语句易用且更加优雅,类似SQLServer的关联更新

      以下操作基于的环境

      SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Fri May 17 11:17:54 2024

      Version 23.2.0.0.0

      5.1、关联更新update

      TESTUSER@FREEPDB1> create table t_emp as select EMPLOYEE_ID,DEPARTMENT_ID,SALARY from employees;
       
      Table created.
       
      TESTUSER@FREEPDB1> desc t_emp;
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       EMPLOYEE_ID                                        NUMBER(6)
       DEPARTMENT_ID                                      NUMBER(4)
       SALARY                                             NUMBER(8,2)
       
      TESTUSER@FREEPDB1> select * from t_emp where D编程EPARTMENT_ID=110;
       
      EMPLOYEE_ID DEPARTMENT_ID     SALARY
      ----------- ------------- ----------
              205           110      12008
              206           110       8300
       
      TESTUSER@FREEPDB1> update t_emp set DEPARTMENT_ID=null,SALARY=null where DEPARTMENT_ID=110;
       
      2 rows updated.
       
      TESTUSER@FREEPDB1> commit;
       
      Commit complete.
       
      TESTUSER@FREEPDB1> select * from t_emp where DEPARTMENT_ID is null;
       
      EMPLOYEE_ID DEPARTMENT_ID     SALARY
      ----------- ------------- ----------
              178                     7000
              205
              206
      -- oracle 23c SQL增强 表关联更新        
      TESTUSER@FREEPDB1> update t_emp t1 set t1.DEPARTMENT_ID=t2.DEPARTMENT_ID,t1.SALARY=t2.SALARY from employees t2 where t2.EMPLOYEE_ID=t1.EMPLOYEE_ID and t1.DEPARTMENT_ID is null;
       
      3 row updated.
       
       
      TESTUSER@FREEPDB1> 编程客栈commit;
       
      Commit complete.
       
      TESTUSER@FREEPDB1> select t1.* from t_emp t1 where t1.DEPARTMENT_ID=110;
      EMPLOYEE_ID DEPARTMENT_ID     SALARY
      ----------- ------------- ----------
              205           110      12008
              206           110       8300
       

      5.2、关联删除delete

      TESTUSER@FREEPDB1> delete t_emp t1 from employees t2 where t2.EMPLOYEE_ID=t1.EMPLOYEE_ID and t2.DEPARTMENT_ID=110;
       
      45 rows deleted.
       
       
      TESTUSER@FREEPDB1> commit;
       
      Commit complete.
       
       
      TESTUSER@FREEPDB1> select t1.* from t_emp t1 where t1.DEPARTMENT_ID=110;
       
      no rows selected

      以上就是Oracle表关联更新几种方法小结的详细内容,更多关于Oracle表关联更新的资料请关注编程客栈(www.devze.com)其它相关文章!

      0

      上一篇:

      下一篇:

      精彩评论

      暂无评论...
      验证码 换一张
      取 消

      最新数据库

      数据库排行榜