[SOLVED] Oracle Merge: When not matched then delete and insert

Oracle Merge: When not matched then delete and insert

I have a simple query but just one part confuses me:

I have the following algorithm:

merge into table_1 table_2
on table_1.val1 = table_2.val1
when matched and table_1.val2 = table_2.val2
   then merge
when matched and table_1.val2 != table_2.val2
   then delete and insert ( I AM NOT SURE NOW TO DO THIS)
when not matched
   then insert;

Can you hep me with the delete and insert or tell me a way around?

Solution

You cannot insert in the WHEN MATCHED THEN UPDATE clause. You can only DELETE here. And why do you need to delete and then insert if you can just update? Something like this:

merge into table_1 
using table_2 on (table_1.val1 = table_2.val1)
when matched then 
     update set table_1.val3 = 
                case when table_1.val2 = table_2.val2 
                     then table_1.val3 
                else table_2.val3 end
when not matched then insert ...

From the comments below I suppose you need something like this:

1) There is no foreign keys which reference table_1
2) Disable the primary key val2 in table_1
3) update table_1 set val2 = null;
4) run merge
5) delete from table_1 where val2 is null;
6) Enable the primary key in table_1