create table test_main 
  (
    id number primary key,
    ag_num number,
    mdate date ,
    value number
  );

create table test_log
  (
    fk_ag_num number,
    ldate date, 
    value number, 
    fk_id number
  );


insert all 
insert all 
  into test_main(id, ag_num, mdate, value) values (10, 50, trunc(sysdate), -15.5)
  into test_main(id, ag_num, mdate, value) values (22, 50, trunc(sysdate), -15.5)
  into test_main(id, ag_num, mdate, value) values (13, 60, trunc(sysdate), -115.5)
  into test_main(id, ag_num, mdate, value) values (400, 60, trunc(sysdate), -115.5)
  into test_main(id, ag_num, mdate, value) values (51, 90, trunc(sysdate), -215.5)
  into test_main(id, ag_num, mdate, value) values (16, 90, trunc(sysdate), -215.5)
  
  into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (50, trunc(sysdate), -15.5)
  into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (50, trunc(sysdate), -15.5)
  into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (60, trunc(sysdate), -115.5)
  into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (60, trunc(sysdate), -115.5)
  into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (90, trunc(sysdate), -215.5)
  into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (90, trunc(sysdate), -215.5)
select * from dual;   
  
  into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (50, trunc(sysdate), -15.5)
  into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (50, trunc(sysdate), -15.5)
  into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (60, trunc(sysdate), -115.5)
  into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (60, trunc(sysdate), -115.5)
  into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (90, trunc(sysdate), -215.5)
  into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (90, trunc(sysdate), -215.5)
select * from dual;      

select rownum ord, due.* from test_main due where mdate = trunc(sysdate) and  value < 0 order by due.ag_num;

select rownum ord, trn.* from TEST_LOG trn where trn.ldate = trunc(sysdate) and value < 0 order by trn.FK_AG_NUM;



MERGE INTO (select rownum ord, trn.* from TEST_LOG trn where trn.ldate = trunc(sysdate) and value < 0 order by trn.FK_AG_NUM) e
            USING (
                    select rownum ord, due.* from test_main due where mdate = trunc(sysdate) and  value < 0 order by due.ag_num
                  ) h
            ON (e.FK_AG_NUM = h.ag_num and e.ldate = h.mdate and e.value = h.value and e.ord = h.ord)
          WHEN MATCHED THEN
            UPDATE SET e.fk_id = h.id
          ;

merge
  into (
      select rowid as rid, fk_id
      from   test_log
      where  ldate = trunc(sysdate) and value < 0
    ) tgt
  using (
      with
        m (id, ag_num, mdate, value, ord) as (
          select tm.*, rownum
          from   test_main tm
          where  mdate = trunc(sysdate) and value < 0
          order  by ag_num
        )
      , l (rid, fk_ag_num, ldate, value, fk_id, ord) as (
          select tl.rowid, tl.*, rownum
          from   test_log tl
          where  ldate = trunc(sysdate) and value < 0
          order  by fk_ag_num
        )
      select l.rid as rid, m.id as id
      from   m join l on l.fk_ag_num = m.ag_num and l.value = m.value and l.ord = m.ord
    ) src
  on (tgt.rid = src.rid)
when matched then update set tgt.fk_id = src.id
;