Is Oracle MERGE NOT MATCHED THEN UPDATE possible?

Question

We'd like to set the IS_DEL bit = 1 when a record exists in a Target table that doesn't exist in the Source table.

Is it possible to use a MERGE statement using the WHEN NOT MATCHED clause, but make it perform an UPDATE?

When attempting to do so, I'm getting a "ORA-00905: missing keyword" message.

MERGE
INTO    AMEPSA.ENTERPRISE_LOCATION trg
USING   (
        SELECT C.LOCATION_KEY as LOCATION_KEY
        FROM AMEPSA.ENTERPRISE_LOCATION C
            INNER JOIN AMESTAGE.VW_LOCATION L ON C.REC_SRC_KEY_CD = L.LOCATION_ID    
        WHERE C.CURR_REC_IND = 'Y'
        ) src
ON      (trg.LOCATION_KEY = src.LOCATION_KEY)
WHEN NOT MATCHED THEN UPDATE
    SET trg.IS_DEL = 1

Does the "WHEN NOT MATCH" clause only support "THEN INSERT"?

WHEN MATCHED THEN UPDATE updates the matching rows. Which row(s) would you expect the UPDATE to affect when there is no match? — Matthew McPeak
Trying to update the Target table if there is no match. Trying to set the IS_DEL bit = 1 in Target table if an ID exists in Target table which doesn't exist in Source table anymore. — Matthew Walk

Alex Poole Alex Poole · Accepted Answer · 2017-08-10T18:30:16

From the documentation:

Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view.

The syntax looks for rows in the source table (src) which do or do not have matching rows in the target table (trg). If there is a matching target row then it updates that; if there is not a matching row then it inserts a new row in the target table.

It does not, and cannot, look for rows in the target table that are not matched in the source table - which is what you are trying to identify and update.

The syntax diagrams for WHEN MATCHED and WHEN NOT MATCHED also make it clear that you cannot do WHEN NOT MATCHED THEN UPDATE.

Is Oracle MERGE NOT MATCHED THEN UPDATE possible?

2 Answers