If I understand correctly, you want to synchronize A to B. In other words, A should become identical to B.
To do that, I suggest using Tom Kyte's "compare tables" method to find what needs to be inserted, updated or deleted, then merge to A using the result. For example:
drop table a purge;
create table a(
ID, REF, NO, NAME) as select
0434, 98, 93, 'ABC' from dual union all select
0423, 18, 61, 'XYZ' from dual union all select
4875, 474, 45, 'tyh' from dual;
drop table b purge;
create table b(
ID, REF, NO, NAME) as select
0434, 99, 93, 'ABC' from dual union all select
0423, 18, 61, 'XYZ' from dual union all select
0476, 45, 56, 'rrt' from dual;
merge into A O
using (
select * from (
select
"ID", "REF", "NO", "NAME",
COUNT(*) over(partition by ID)
- SUM(Z##_CNT) Z##IUD_FLAG
from (
select
"ID", "REF", "NO", "NAME",
-1 Z##_CNT
from A O
union all
select
"ID", "REF", "NO", "NAME",
1 Z##_CNT
from B N
)
group by
"ID", "REF", "NO", "NAME"
having SUM(Z##_CNT) != 0
)
where Z##IUD_FLAG < 3
) N
on (
O.ID=N.ID
)
when matched then update set
"NAME"=N."NAME", "NO"=N."NO", "REF"=N."REF"
delete where N.Z##IUD_FLAG = 2
when not matched then insert (
"ID", "REF", "NO", "NAME"
) values(
N."ID", N."REF", N."NO", N."NAME"
);The advantage of this method is that it does minimal changes to the target table. If you run this statement twice, the second time you will get "0 rows merged".
This statement was generated from the COMPARE_SYNC package I wrote. You can find it here:
https://stewashton.wordpress.com/2015/01/21/stew_compare_sync-introducing-the-package/