1. Test table and data preparation
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;
-- Query tables T_update01, T_update02
select * from T_update01;
ID INFONAME SYS_GUID
---------- ------------------------------ ------------------------------------
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 189F5A1099C66606E0639C0AA8C0F15E2、update set column ... where exists
2.1. Update set Single column field
-- update set single column field, update all data that meets the associated conditions
update T_update01 T1
set infoname=(select from T_update02 T2 where =)
where exists (select 1 from T_update02 T2 where = );
-- update set single column field to update data that meets the specific condition ID=1
update T_update01 T1
set infoname=(select from T_update02 T2 where =)
where =1;
-- This execution update meets the specific conditions of the T_update01 table ID=1
SCOTT@prod02> select * from T_update01;
ID INFONAME SYS_GUID
---------- ------------------------------ ------------------------------------
1 update_set_exists 189F5A1099BF6606E0639C0AA8C0F15E
2 2_updateName 189F5A1099C06606E0639C0AA8C0F15E2.2. Update set Multi-column field
-- T_update01 table inserts one more row of data
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, from T_update02 T2 where =)
where exists (select 1 from T_update02 T2 where = );
commit;
-- After update, check that the values of the two columns sys_guid and infoname are the same as 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 189F5A1099C66606E0639C0AA8C0F15E3. Use cursor
-- T_update02 data is updated to facilitate the display of the results of update using cursors
update T_update02 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
-- Use the INFONAME field of T_update01 with cursor to update it and 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;
/
-- Check the query results
select * from T_update01;
ID INFONAME SYS_GUID
---------- ------------------------------ ------------------------------------
1 update_set_exists 189F5A1099C46606E0639C0AA8C0F15E
2 cursor is select 189F5A1099C56606E0639C0AA8C0F15E
3 cursor is select 189F5A1099C66606E0639C0AA8C0F15E4. merge into clause
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 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 (=)
when matched then update set infoname=
When not matched then insert (ID,infoname,sys_guid) values( ,,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
-- You can find that the INFONAME=merge_into_Name1 of the T_merg01 table ID=1 value is the same as the INFONAME=merge_into_Name1 of the T_merg02 table ID=1
-- You can find that there is one more row of data in the T_merg01 table, which is the row of data with the ID=3 of the table.5. New features of Oracle 23c/AI
Whether it is the published version of Oracle23c free or the long-term supported Oracle23Ai that was released, the table association update update and delete delete statements are easy to use and more elegant, similar to the SQLServer-like association update.
The following operations are based on the environment
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. Related update 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 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 Enhanced Table Association Update
TESTUSER@FREEPDB1> update t_emp t1 set t1.DEPARTMENT_ID=t2.DEPARTMENT_ID,= 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 83005.2. Delete associated
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