create table TABLE_A (key1,key2,key3,seq,countA) as
select 'AAAAA','BB','CC',1,888 from dual union
select 'AAAAA','BB','CC',2,222 from dual union
select 'AAAAA','BB','CC',3,777 from dual union
select 'DDDDD','EE','FF',1,555 from dual union
select 'DDDDD','EE','FF',2,666 from dual union
select 'DDDDD','EE','FF',3,444 from dual union
select 'DDDDD','EE','FF',4,333 from dual;
alter table TABLE_A add primary key(key1,key2,key3,seq);
create table TABLE_B (key1,key2,key3,seq,countB) as
select 'AAAAA','BB','CC',1,123 from dual union
select 'AAAAA','BB','CC',2,456 from dual union
select 'AAAAA','BB','CC',3,789 from dual union
select 'AAAAA','BB','CC',4,123 from dual union
select 'AAAAA','BB','CC',5,456 from dual union
select 'AAAAA','BB','CC',6,789 from dual union
select 'DDDDD','EE','FF',1,123 from dual union
select 'DDDDD','EE','FF',2,456 from dual;
alter table TABLE_B add primary key(key1,key2,key3,seq);
--■■■BYPASS_UJVCを使う方法■■■
update
(select /*+ BYPASS_UJVC */
a.countA as NewValue,b.countB as oldValue
from TABLE_A a join TABLE_B b
on (a.key1 = b.key1
and a.key2 = b.key2
and a.key3 = b.key3)
where a.seq = (select max(c.seq) from TABLE_A c
where c.key1 = a.key1
and c.key2 = a.key2
and c.key3 = a.key3)
and b.seq = (select max(c.seq) from TABLE_B c
where c.key1 = a.key1
and c.key2 = a.key2
and c.key3 = a.key3))
set oldValue = NewValue;
--■■■ベタなupdate文■■■
update TABLE_B b
set countB = (select distinct
Last_Value(countA)
over(order by seq Rows between Unbounded Preceding and Unbounded Following)
from TABLE_A a
where a.key1 = b.key1
and a.key2 = b.key2
and a.key3 = b.key3)
where seq = (select max(c.seq) from TABLE_B c
where c.key1 = b.key1
and c.key2 = b.key2
and c.key3 = b.key3)
and exists(select 1 from TABLE_A c
where c.key1 = b.key1
and c.key2 = b.key2
and c.key3 = b.key3);
--■■■さらにベタなupdate文■■■
update TABLE_B b
set countB = (select countA
from TABLE_A a
where a.key1 = b.key1
and a.key2 = b.key2
and a.key3 = b.key3
and a.seq = (select max(c.seq) from TABLE_A c
where c.key1 = a.key1
and c.key2 = a.key2
and c.key3 = a.key3))
where seq = (select max(c.seq) from TABLE_B c
where c.key1 = b.key1
and c.key2 = b.key2
and c.key3 = b.key3)
and exists(select 1 from TABLE_A c
where c.key1 = b.key1
and c.key2 = b.key2
and c.key3 = b.key3);
--■■■mergeを使う方法(9iなら無意味なinsert文が必要)■■■
merge into TABLE_B aa
using (select b.Row_ID,a.countA
from (select key1,key2,key3,countA,
seq,max(seq) over(partition by key1,key2,key3) as maxSeq
from TABLE_A) a Join
(select key1,key2,key3,RowID as Row_ID,
seq,max(seq) over(partition by key1,key2,key3) as maxSeq
from TABLE_B) b
using (key1,key2,key3)
where a.seq = a.maxSeq
and b.seq = b.maxSeq) bb
on (aa.RowID=bb.Row_ID)
when matched then
update set aa.countB= bb.countA;