两表关联更新

环境介绍：

根据业务表的object_id 和 对照表中的dm_old的对照关系，更新业务表的object_id为 对照表中的dm_new

--1. 对照表1.1 drop table hy0921_tmp purge;1.2 create table hy0921_tmp(dm_old number,dm_new number,dm_name varchar2(50));  1.3 insert into hy0921_tmp(dm_old,dm_name) select t1.OBJECT_ID,t1.OBJECT_NAME from dba_objects t1 where  t1.owner='SCOTT' ;

1.4 对照表中数据如下：

select t1.dm_old,t1.dm_new,t1.dm_name from hy0921_tmp t1  ;

1.5 更新对照表中的字段 dm_new

update hy0921_tmp set dm_new=trunc(dbms_random.value(1,100));

1.6 再次确认对照表的数据

select t1.dm_old,t1.dm_new,t1.dm_name from hy0921_tmp t1  ;

 

 --2. 业务表

2.1 drop table hy0921 purge;

2.2 insert into hy0921 select * from dba_objects t1  where  t1.owner='SCOTT' ;

2.3 select t1.object_id,t1.object_name from hy0921 t1

--3 关联对照表和业务表，确认数据

select distinct 'hy0921',       t1.object_id,       t1.object_name,       'hy0921_tmp',       t2.dm_old,       t2.dm_new,       t2.dm_name  from hy0921 t1, hy0921_tmp t2 where t1.object_id = t2.dm_old --and t1.object_name='SYS_LOG' order by t1.object_id asc;

--4 对照表和业务表关联更新update hy0921 t1   set t1.object_id =       (select dm_new from hy0921_tmp where dm_old = t1.object_id) where exists (select 1 from hy0921_tmp where dm_old = t1.object_id);

--5 再次关联对照表和业务表，确认数据

select distinct 'hy0921',       t1.object_id,       t1.object_name,       'hy0921_tmp',       t2.dm_old,       t2.dm_new,       t2.dm_name  from hy0921 t1, hy0921_tmp t2 where t1.object_id = t2.dm_old      --此处还用的是  对照表中的 dm_old ，肯定没数据 order by t1.object_id asc;

 select distinct 'hy0921',       t1.object_id,       t1.object_name,       'hy0921_tmp',       t2.dm_old,       t2.dm_new,       t2.dm_name  from hy0921 t1, hy0921_tmp t2 where t1.object_id = t2.dm_new   --此处还用的是  对照表中的 dm_new ，数据正常 order by t1.object_id asc;

 

 以下sql效率很好，但是 重复和执行 结果会变！！！

declare  v_rowid varchar2(50);  type ridarray is table of rowid;  type obj#array is table of hy0921_tmp.dm_old%type;  type objnamearray is table of hy0921_tmp.dm_name%type;  my_rids    ridarray;  my_obj#    obj#array;  my_objname objnamearray;  cursor my_cur is    select t2.rowid, t1.dm_new, t2.object_name      from hy0921_tmp t1, hy0921 t2     where t1.dm_old = t2.object_id; --两表的关联条件begin  open my_cur;  loop    fetch my_cur bulk collect      into my_rids, my_obj#, my_objname limit 100;      forall i in 1 .. my_rids.count          update hy0921 t2         set t2.object_id = my_obj#(i)       where rowid = my_rids(i);      commit;      exit when my_cur%notfound;  end loop;  close my_cur;end;--已完成，耗时 261.38 秒

 

over

转载于:https://www.cnblogs.com/iyoume2008/p/7649596.html