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
---------- ------------------------------ ------------------------------------1 1_updateName                   189F5A1099BF6606E0639C0AA8C0F15E2 2_updateName                   189F5A1099C06606E0639C0AA8C0F15Eselect * from T_update02;ID INFONAME                       SYS_GUID
---------- ------------------------------ ------------------------------------1 update_set_exists              189F5A1099C46606E0639C0AA8C0F15E2 update_set_cursor              189F5A1099C56606E0639C0AA8C0F15E3 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              189F5A1099BF6606E0639C0AA8C0F15E2 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              189F5A1099BF6606E0639C0AA8C0F15E2 2_updateName                   189F5A1099C06606E0639C0AA8C0F15E3 insert03                       189F5A1099C76606E0639C0AA8C0F15Eupdate 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              189F5A1099C46606E0639C0AA8C0F15E2 update_set_cursor              189F5A1099C56606E0639C0AA8C0F15E3 3_Name                         189F5A1099C66606E0639C0AA8C0F15Eselect * from T_update02;ID INFONAME                       SYS_GUID
---------- ------------------------------ ------------------------------------1 update_set_exists              189F5A1099C46606E0639C0AA8C0F15E2 update_set_cursor              189F5A1099C56606E0639C0AA8C0F15E3 3_Name                         189F5A1099C66606E0639C0AA8C0F15E

3、使用游標

-- T_update02數據更新一下，方便使用游標更新的結果顯示
update T_update02 set INFONAME='cursor is select' where id>=2;
commit;
select * from T_update02;ID INFONAME                       SYS_GUID
---------- ------------------------------ ------------------------------------1 update_set_exists              189F5A1099C46606E0639C0AA8C0F15E2 cursor is select               189F5A1099C56606E0639C0AA8C0F15E3 cursor is select               189F5A1099C66606E0639C0AA8C0F15E-- 使用用游標更新T_update01的INFONAME字段，使其和T_update02 where id>=2
declarecursor cur_my_source is select infoname,id from T_update02;beginfor cur_my_target in cur_my_source loopupdate 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              189F5A1099C46606E0639C0AA8C0F15E2 cursor is select               189F5A1099C56606E0639C0AA8C0F15E3 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
---------- ------------------------------ ------------------------------------1 1_Name                         189F5A1099BB6606E0639C0AA8C0F15E2 2_Name                         189F5A1099BC6606E0639C0AA8C0F15Einsert 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               189F5A1099BD6606E0639C0AA8C0F15E3 3_Name                         189F5A1099BE6606E0639C0AA8C0F15Emerge 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               189F5A1099BB6606E0639C0AA8C0F15E2 2_Name                         189F5A1099BC6606E0639C0AA8C0F15E3 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 DEPARTMENT_ID=110;EMPLOYEE_ID DEPARTMENT_ID     SALARY
----------- ------------- ----------205           110      12008206           110       8300TESTUSER@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                     7000205206
-- 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      12008206           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