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"?

Answer 1

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.

Answer 2

Yes you can only insert when not match. See exact options in oracle merge.

The condition can refer to either the data source or the target table. If the condition is not true, then the database skips the update operation when merging the row into the table.