merge into when not matched insert if condition

Question

I have the following query:

merge into A a 
using (select :1 as x, :2 as y from sys.dual) tmp 
on (a.x = tmp.x and 
    a.y = tmp.y) 
when matched then 
   update set a.z = case when :3 = 1 then :4 else null end 
when not matched then 
   insert 
   ( 
    x, 
    y, 
    z 
   )
   values 
   ( 
    :1, 
    :2, 
    case when :3 = 1 then :4 else null end
   )

which works, however a new record is inserted when :3 is 0. The z value of that record is null. I'd rather not have a record inserted in case :3 is 0.

Is there any way to do this?

Answer 1

As per [the 11.2 documentation] you can have a where clause on the insert clause, so your merge statement would become something like:

merge into A a 
using (select :1 as x, :2 as y from sys.dual) tmp 
on (a.x = tmp.x and 
    a.y = tmp.y) 
when matched then 
   update set a.z = case when :3 = 1 then :4 else null end 
when not matched then 
   insert 
   ( 
    x, 
    y, 
    z 
   )
   values 
   ( 
    :1, 
    :2, 
    case when :3 = 1 then :4 else null end
   )
   where :3 != 0 or :3 is null;

N.B. untested