sql - 合并到不匹配时插入 if 条件

Question

我有以下查询:

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
   )

这有效，但是当 :3 为 0 时，将插入一条新记录。该记录的 z 值为空。如果 :3 为 0，我宁愿不插入记录。

有什么办法吗？

Answer 1

根据 [the 11.2 documentation] 你可以在插入子句上有一个 where 子句，所以你的合并语句会变成这样:

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;

注意未经测试