4

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

Matthew Walk
  • 1,014
  • 2
  • 16
  • 36
  • 1
    Look at : https://stackoverflow.com/questions/10539627/when-doing-a-merge-in-oracle-sql-how-can-i-update-rows-that-arent-matched-in-t – mehmet sahin Aug 10 '17 at 17:22
  • 1
    `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 Aug 10 '17 at 17:56
  • What would you update if there is no match? – Sal Aug 10 '17 at 18:44
  • 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 Aug 10 '17 at 22:03

2 Answers2

6

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
1

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.

Ori Marko
  • 56,308
  • 23
  • 131
  • 233