Oracle表关联更新几种方法

1、测试表及数据准备

1. create table T_update01(ID int ,infoname varchar2(32),sys_guid varchar2(36));
2. create table T_update02(ID int ,infoname varchar2(32),sys_guid varchar2(36));
4. insert into T_update01
5. select 1,N'1_updateName',sys_guid() from dual
6. union
7. select 2,N'2_updateName',sys_guid() from dual;
8. commit;
10. insert into T_update02
11. select 1,N'update_set_exists',sys_guid() from dual;
12. insert into T_update02
13. select 2,N'update_set_cursor',sys_guid() from dual;
14. insert into T_update02
15. select 3,N'3_Name',sys_guid() from dual;
16. commit;
18. -- 查询表T_update01、T_update02
19. select * from T_update01;
21.         ID INFONAME                       SYS_GUID
22. ---------- ------------------------------ ------------------------------------
23.          1 1_updateName                   189F5A1099BF6606E0639C0AA8C0F15E
24.          2 2_updateName                   189F5A1099C06606E0639C0AA8C0F15E
26. select * from T_update02;
28.         ID INFONAME                       SYS_GUID
29. ---------- ------------------------------ ------------------------------------
30.          1 update_set_exists              189F5A1099C46606E0639C0AA8C0F15E
31.          2 update_set_cursor              189F5A1099C56606E0639C0AA8C0F15E
32.          3 3_Name                         189F5A1099C66606E0639C0AA8C0F15E

复制代码

2、update set column ... where exists


2.1、update set 单列字段

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

复制代码

2.2、update set 多列字段

1. -- T_update01表多插入一行数据
2. insert into T_update01
3. select 3,N'insert03',sys_guid() from dual;
4. commit;
6. select * from T_update01;
8.         ID INFONAME                       SYS_GUID
9. ---------- ------------------------------ ------------------------------------
10.          1 update_set_exists              189F5A1099BF6606E0639C0AA8C0F15E
11.          2 2_updateName                   189F5A1099C06606E0639C0AA8C0F15E
12.          3 insert03                       189F5A1099C76606E0639C0AA8C0F15E
14. update T_update01 T1
15. set (sys_guid,infoname) = (select T2.sys_guid,T2.infoname from T_update02 T2 where T2.ID=T1.ID)
16. where exists (select 1 from T_update02 T2 where T2.ID=T1.ID );
17. commit;
18. -- 更新后检查，sys_guid,infoname两列的值和T_update02一样了
19. select * from T_update01;
21.         ID INFONAME                       SYS_GUID
22. ---------- ------------------------------ ------------------------------------
23.          1 update_set_exists              189F5A1099C46606E0639C0AA8C0F15E
24.          2 update_set_cursor              189F5A1099C56606E0639C0AA8C0F15E
25.          3 3_Name                         189F5A1099C66606E0639C0AA8C0F15E
27. select * from T_update02;
29.         ID INFONAME                       SYS_GUID
30. ---------- ------------------------------ ------------------------------------
31.          1 update_set_exists              189F5A1099C46606E0639C0AA8C0F15E
32.          2 update_set_cursor              189F5A1099C56606E0639C0AA8C0F15E
33.          3 3_Name                         189F5A1099C66606E0639C0AA8C0F15E

复制代码

3、利用游标

1. -- T_update02数据更新一下，方便使用游标更新的结果显示
2. update T_update02 set INFONAME='cursor is select' where id>=2;
3. commit;
4. select * from T_update02;
6.         ID INFONAME                       SYS_GUID
7. ---------- ------------------------------ ------------------------------------
8.          1 update_set_exists              189F5A1099C46606E0639C0AA8C0F15E
9.          2 cursor is select               189F5A1099C56606E0639C0AA8C0F15E
10.          3 cursor is select               189F5A1099C66606E0639C0AA8C0F15E
12. -- 使用用游标更新T_update01的INFONAME字段，使其和T_update02 where id>=2
13. declare
14.   cursor cur_my_source is select infoname,id from T_update02;
15.   begin
16.      for cur_my_target in cur_my_source loop
17.        update T_update01 set infoname=cur_my_target.infoname where id=cur_my_target.id;
18.      end loop;
19.          commit;
20. end;
21. /
23. -- 检查查询结果
24. select * from T_update01;
26.         ID INFONAME                       SYS_GUID
27. ---------- ------------------------------ ------------------------------------
28.          1 update_set_exists              189F5A1099C46606E0639C0AA8C0F15E
29.          2 cursor is select               189F5A1099C56606E0639C0AA8C0F15E
30.          3 cursor is select               189F5A1099C66606E0639C0AA8C0F15E

复制代码

4、merge into子句

1. create table T_merg01(ID int ,infoname varchar2(32),sys_guid varchar2(36));
2. create table T_merg02(ID int ,infoname varchar2(32),sys_guid varchar2(36));
4. insert into T_merg01
5. select 1,N'1_Name',sys_guid() from dual
6. union
7. select 2,N'2_Name',sys_guid() from dual;
8. commit;
10. select * from T_merg01;
11.         ID INFONAME                       SYS_GUID
12. ---------- ------------------------------ ------------------------------------
13.          1 1_Name                         189F5A1099BB6606E0639C0AA8C0F15E
14.          2 2_Name                         189F5A1099BC6606E0639C0AA8C0F15E
17. insert into T_merg02
18. select 1,N'merge_into_Name1',sys_guid() from dual;
20. insert into T_merg02
21. select 3,N'3_Name',sys_guid() from dual;
23. select * from T_merg02;
25.         ID INFONAME                       SYS_GUID
26. ---------- ------------------------------ ------------------------------------
27.          1 merge_into_Name1               189F5A1099BD6606E0639C0AA8C0F15E
28.          3 3_Name                         189F5A1099BE6606E0639C0AA8C0F15E
31. merge into T_merg01 T1
32. using T_merg02 T2 on (T1.id=T2.id)
33. when matched then update set infoname=T2.infoname
34. when not matched then insert (ID,infoname,sys_guid) values(T2.ID ,T2.infoname,T2.sys_guid);
36. commit;
38. select * from T_merg01;
39.         ID INFONAME                       SYS_GUID
40. ---------- ------------------------------ ------------------------------------
41.          1 merge_into_Name1               189F5A1099BB6606E0639C0AA8C0F15E
42.          2 2_Name                         189F5A1099BC6606E0639C0AA8C0F15E
43.          3 3_Name                         189F5A1099BE6606E0639C0AA8C0F15E
44. -- 可以发现T_merg01表的ID=1的INFONAME=merge_into_Name1和T_merg02表ID=1的值一样了
45. -- 可以发现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

1. TESTUSER@FREEPDB1> create table t_emp as select EMPLOYEE_ID,DEPARTMENT_ID,SALARY from employees;
3. Table created.
5. TESTUSER@FREEPDB1> desc t_emp;
6. Name                                      Null?    Type
7. ----------------------------------------- -------- ----------------------------
8. EMPLOYEE_ID                                        NUMBER(6)
9. DEPARTMENT_ID                                      NUMBER(4)
10. SALARY                                             NUMBER(8,2)
12. TESTUSER@FREEPDB1> select * from t_emp where DEPARTMENT_ID=110;
14. EMPLOYEE_ID DEPARTMENT_ID     SALARY
15. ----------- ------------- ----------
16.         205           110      12008
17.         206           110       8300
19. TESTUSER@FREEPDB1> update t_emp set DEPARTMENT_ID=null,SALARY=null where DEPARTMENT_ID=110;
21. 2 rows updated.
23. TESTUSER@FREEPDB1> commit;
25. Commit complete.
27. TESTUSER@FREEPDB1> select * from t_emp where DEPARTMENT_ID is null;
29. EMPLOYEE_ID DEPARTMENT_ID     SALARY
30. ----------- ------------- ----------
31.         178                     7000
32.         205
33.         206
34. -- oracle 23c SQL增强 表关联更新        
35. 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;
37. 3 row updated.
40. TESTUSER@FREEPDB1> commit;
42. Commit complete.
44. TESTUSER@FREEPDB1> select t1.* from t_emp t1 where t1.DEPARTMENT_ID=110;
45. EMPLOYEE_ID DEPARTMENT_ID     SALARY
46. ----------- ------------- ----------
47.         205           110      12008
48.         206           110       8300

复制代码

5.2、关联删除delete

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

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。