Hi,
Connor's answer is perfect as far as it goes.
If the MERGE does only updates, it can do an inner join of the two tables.
If the MERGE does updates and inserts, it can do "target right join source". Rows in source but not in target are then inserted.
The MERGE cannot directly do the equivalent of a full outer join - at least in Oracle - because a row must be updated in order to be eligible for delete.
In order to do DELETEs, the USING clause must contain a query that compares the two tables and produces rows for all three operations. I use the "group by" method popularized by Tom Kyte instead of a full outer join.
merge /*+ append */ into TARGET_TABLE O
using (
select * from (
select PK_COL, COL2, COL3,
COUNT(*) over(partition by PK_COL) - SUM(Z##_CNT) Z##IUD_FLAG
from (
select PK_COL, COL2, COL3,
-1 Z##_CNT
from TARGET_TABLE O
union all
select PK_COL, COL2, COL3,
1 Z##_CNT
from SOURCE_TABLE N
)
group by PK_COL, COL2, COL3
having SUM(Z##_CNT) != 0
)
where Z##IUD_FLAG < 3
) N
on (O.PK_COL=N.PK_COL)
when matched then update set
COL2=N.COL2, COL3=N.COL3
delete where N.Z##IUD_FLAG = 2
when not matched then insert (PK_COL, COL2, COL3)
values(N.PK_COL, N.COL2, N.COL3);Note the APPEND hint will be honored for any insertions.
Best regards, Stew
P.S. You can generate the MERGE statement using a package I wrote. See
https://stewashton.wordpress.com/2015/01/21/stew_compare_sync-introducing-the-package/