delete from TRANS_LINK
where TRANS_LINK_ID in
(
select tl.TRANS_LINK_ID
from TRANS_LINK tl
join TRANS_BATCH_LINK rs on (tl.TRANS_PROVIDER_ID = rs.TRANS_PROVIDER_ID and tl.LOCAL_CLIENT_ID = rs.LOCAL_CLIENT_ID)
where tl.DATE_BEG > rs.DATE_BEG
)

delete from TRANS_LINK tl
where exists
(
select tl.TRANS_LINK_ID
from TRANS_BATCH_LINK rs
where tl.DATE_BEG > rs.DATE_BEG
and tl.TRANS_PROVIDER_ID = rs.TRANS_PROVIDER_ID
and tl.LOCAL_CLIENT_ID = rs.LOCAL_CLIENT_ID
)

delete from TRANS_LINK where TRANS_LINK_ID in
(
  select tl.TRANS_LINK_ID
  from TRANS_LINK tl
  join TRANS_BATCH_LINK rs on (tl.TRANS_PROVIDER_ID = rs.TRANS_PROVIDER_ID and tl.LOCAL_CLIENT_ID = rs.LOCAL_CLIENT_ID)
  where tl.DATE_BEG > rs.DATE_BEG
)
;

merge into TRANS_LINK data
using
(
  select tl.*
  , rs.TRANS_PROVIDER_ID as NEW_PROVIDER
  , rs.LOCAL_CLIENT_ID as NEW_CLIENT
  , rs.LOCAL_ACCOUNT_ID as NEW_ACCOUNT
  , rs.LOCAL_SERVICE_ID as NEW_SERVICE
  , rs.FIELD as NEW_FIELD
  , rs.VALUE as NEW_VALUE
  , rs.DESCRIPTION as NEW_DESCRIPTION
  , rs.DATE_BEG as NEW_MOMENT
  , D.FLAG
  from TRANS_BATCH_LINK rs
  left join TRANS_LINK tl on (tl.TRANS_PROVIDER_ID = rs.TRANS_PROVIDER_ID and tl.LOCAL_CLIENT_ID = rs.LOCAL_CLIENT_ID and tl.DATE_BEG <= rs.DATE_BEG and (tl.DATE_END is null or tl.DATE_END > rs.DATE_BEG))
  left join (select null as FLAG from dual union all select 1 from dual) D on (tl.TRANS_LINK_ID is not null)
) rs on (rs.TRANS_LINK_ID = data.TRANS_LINK_ID and rs.FLAG is not null)
when matched then
  update set data.DATE_END = rs.NEW_MOMENT
when not matched then
  insert (data.TRANS_PROVIDER_ID, data.LOCAL_CLIENT_ID, data.LOCAL_ACCOUNT_ID, data.LOCAL_SERVICE_ID, data.FIELD,     data.VALUE,     data.DESCRIPTION,     data.DATE_BEG,   data.DATE_END)
  values (  rs.NEW_PROVIDER,        rs.NEW_CLIENT,        rs.NEW_ACCOUNT,        rs.NEW_SERVICE,        rs.NEW_FIELD,   rs.NEW_VALUE,   rs.NEW_DESCRIPTION,   rs.NEW_MOMENT, null)
;