Adding conditions in MERGE statement in Oracle SQL for INSERT/UPDATE

Question

I have to insert/update some RECORDS in table target_table. These records are coming one source_table.

I am using MERGE for update/insert the target_table.

MERGE    INTO  target_table tgt    USING source_table src    ON  ( src.column1 = tgt.column1 and          src.column2 = tgt.column2) WHEN MATCHED THEN    UPDATE    SET   tgt.column3= src.column3,          tgt.column4 = src.coulmn4    WHEN NOT MATCHED    THEN INSERT ( tgt.column1,          tgt.column2,          tgt.column3,          tgt.column4 ) VALUES ( src.coulmn1,          src.coulmn2,          src.coulmn3,          src.coulmn4); 

I want to add some specific condition on update.

IF target_table.column3 in (val1','val2)

then only there should be update, else no update or insert.

Answer 1

You can simply add WHERE clause to UPDATE. More about it in oracle docs.

So in your case it should look like:

... WHEN MATCHED THEN    UPDATE    SET   tgt.column3= src.column3,          tgt.column4 = src.coulmn4    WHERE tgt.column3 IN (val1, val2)  WHEN NOT MATCHED ... 

Answer 2

Instead Try Doing As below:

MERGE INTO  target_table tgt USING source_table src ON  (src.column1 = tgt.column1) WHEN MATCHED THEN UPDATE SET   tgt.column3= src.column3,      tgt.column4 = src.coulmn4   WHERE src.column1 = tgt.column1 and      src.column2 = tgt.column2 WHEN NOT MATCHED THEN INSERT ( tgt.column1,      tgt.column2,      tgt.column3,      tgt.column4 ) VALUES ( src.coulmn1,      src.coulmn2,      src.coulmn3,      src.coulmn4);