While merging in Oracle 11g, I want to delete destination row that is not matching with source

Question

SQL> SELECT * FROM SOURCE;

 MEMBER_ID FIRST_NAME LAST_NAME  RANK
---------- ---------- ---------- --------------------
         1 Abel       Wolf       Gold
         2 Clarita    Franco     Platinum
         3 Darryl     Giles      Silver
         4 Dorthea    Suarez     Silver
         5 Katrina    Wheeler    Silver
         6 Lilian     Garza      Silver
         7 Ossie      Summers    Gold
         8 Paige      Mcfarland  Platinum
         9 Ronna      Britt      Platinum
        10 Tressie    Short      Bronze

10 rows selected.

SQL> SELECT * FROM DESTINATION;

 MEMBER_ID FIRST_NAME LAST_NAME  RANK
---------- ---------- ---------- --------------------
         1 Abel       Wolf       Silver
         2 Clarita    Franco     Platinum
         3 Darryl     Giles      Bronze
         4 Dorthea    Gate       Gold
         5 Katrina    Wheeler    Silver
         6 Lilian     Stark      Silver
        11 Chris      Well       Silver

7 rows selected.

When I am trying to execute the below query, it is getting executed but not record 11 is getting deleted from destination table:

MERGE INTO DESTINATION D
USING SOURCE S
ON (D.MEMBER_ID = S.MEMBER_ID)
WHEN MATCHED THEN
  UPDATE
     SET D.FIRST_NAME = S.FIRST_NAME,
         D.LAST_NAME  = S.LAST_NAME,
         D.RANK       = S.RANK 
  DELETE
   WHERE D.MEMBER_ID <> S.MEMBER_ID
WHEN NOT MATCHED THEN
  INSERT
    (D.MEMBER_ID, D.FIRST_NAME, D.LAST_NAME, D.RANK)
  VALUES
    (S.MEMBER_ID, S.FIRST_NAME, S.LAST_NAME, S.RANK);

Answer 1

Because the current logic uses INNER JOIN implicitly, and MEMBER_ID = 11 doesn't exist within the table SOURCE, so this values does not incur the MATCHED case, thus never been deleted.

You can use an OUTER JOIN such as FULL JOIN as below

MERGE INTO DESTINATION D
USING (SELECT NVL(S.MEMBER_ID,D.MEMBER_ID) AS MEMBER_ID,S.FIRST_NAME, S.LAST_NAME, S.RANK 
         FROM DESTINATION D
         FULL JOIN SOURCE S
           ON S.MEMBER_ID = D.MEMBER_ID) S
   ON (NVL(S.MEMBER_ID,D.MEMBER_ID) = D.MEMBER_ID)
WHEN MATCHED THEN
  UPDATE
     SET D.FIRST_NAME = S.FIRST_NAME,
         D.LAST_NAME  = S.LAST_NAME,
         D.RANK       = S.RANK 
  DELETE
   WHERE S.FIRST_NAME IS NULL
  WHEN NOT MATCHED THEN
  INSERT
    (D.MEMBER_ID, D.FIRST_NAME, D.LAST_NAME, D.RANK)
  VALUES
    (S.MEMBER_ID, S.FIRST_NAME, S.LAST_NAME, S.RANK);

Demo