SQLで教えてください

update TABLE_B b
set b.countB = (
        select countA from TABLEA d
        where   d.key1 = b.key1 and
                d.key2 = b.key2 and
                d.key3 = b.key3 and
                d.seq >= all (
                        select e.seq from TABLE_A e
                        where   e.key1 = d.key1 and
                                e.key2 = d.key2 and
                                e.key3 = e.key3
                        )
    )
where exists(
    select * from TABLE_B a
    where   a.key1 = b.key1 and
            a.key2 = b.key2 and
            a.key3 = b.key3 and
            a.seq >= all (
                    select c.seq from TABLE_B c
                    where   c.key1 = a.key1 and
                            c.key2 = a.key2 and
                            c.key3 = a.key3
                    )
    )

create table TABLE_A (key1,key2,key3,seq,countA) as
select 'AAAAA','BB','CC',1,11 from dual union
select 'AAAAA','BB','CC',2,22 from dual union
select 'AAAAA','BB','CC',3,33 from dual union
select 'DDDDD','EE','FF',1,44 from dual union
select 'DDDDD','EE','FF',2,55 from dual union
select 'DDDDD','EE','FF',3,66 from dual union
select 'DDDDD','EE','FF',4,77 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,999 from dual union
select 'AAAAA','BB','CC',2,999 from dual union
select 'AAAAA','BB','CC',3,999 from dual union
select 'AAAAA','BB','CC',4,999 from dual union
select 'AAAAA','BB','CC',5,999 from dual union
select 'AAAAA','BB','CC',6,999 from dual union
select 'DDDDD','EE','FF',1,999 from dual union
select 'DDDDD','EE','FF',2,999 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
(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 /*+ BYPASS_UJVC */
(select
 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;