我要评论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 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 单列字段
-- 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 189f5a1099c06606e0639c0aa8c0f15e2.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 189f5a1099c66606e0639c0aa8c0f15e3、使用游标
-- 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 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 189f5a1099c66606e0639c0aa8c0f15e4、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 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 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增强 表关联更新
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表关联更新的资料请关注代码网其它相关文章！
发表评论