为什么合并为UPSERT不工作的空表?

Question

事实表(目标)为空(没有行)。当我执行下面的代码时，它不能用于插入，只能用于更新。

merge into fact as f
using (select * from fact where date_id = 429 and region_id = 432 and attack_id = 5
and target_id = 11 and gname_id = 12 and weapon_id = 12 and success = 1 and claimed = 1 and ishostkid = 0 ) as t
on (f.date_id = t.date_id and f.region_id = t.region_id and f.attack_id = t.attack_id and f.target_id = t.target_id and
f.gname_id = t.gname_id and f.weapon_id= t.weapon_id and f.success = t.success and f.claimed = t.claimed and f.ishostkid = t.ishostkid)
when MATCHED then
update set num_attack = f.num_attack + 1
when not matched by target then
insert values (429,432,5,11,12,12,1,1,0,1);

既然没有匹配(空表)如何插入数据?

当有行和匹配时，命令可以正常工作。

这只是一个示例代码，这些值是由?使用python和ODBC模块动态更改的。

Answer 1

对于UPSERT，您需要一个构造的值表，而不是实际表，例如，只需选择如下所示的新值(或使用VALUES子句)，然后始终存在一行，要么匹配，要么不匹配，然后您得到更新或插入相应的

merge into Fact as f -- target
using (
select 429 as date_id
, 432 as region_id
, 5 as attack_id
, 11 as target_id
, 12 as gname_id
, 12 as weapon_id
, 1 as success
, 1 as claimed
, 0 as ishostkid
) as t -- source (the 't' alias could be confusing here)
on (
f.date_id = t.date_id
and f.region_id = t.region_id
and f.attack_id = t.attack_id
and f.target_id = t.target_id
and f.gname_id = t.gname_id
and f.weapon_id = t.weapon_id
and f.success = t.success
and f.claimed = t.claimed
and f.ishostkid = t.ishostkid
)
when matched then
update set num_attack = f.num_attack + 1
when not matched by target then
insert values (t.date_id, t.region_id, t.attack_id, t.target_id, t.gname_id, t.weapon_id, t.success, t.claimed, t.ishostkid, 1);

Answer 2

您可以重新排列查询，以便源使用values子句，即。表值构造函数…

merge into fact as f
using (
select * from (values
(429,432,5,11,12,12,1,1,0,1)
) vals (date_id, region_id, attack_id, target_id, gname_id, weapon_id, success, claimed, ishostkid, num_attack)
) as t
on (f.date_id = t.date_id and f.region_id = t.region_id and f.attack_id = t.attack_id and f.target_id = t.target_id and
f.gname_id = t.gname_id and f.weapon_id= t.weapon_id and f.success = t.success and f.claimed = t.claimed and f.ishostkid = t.ishostkid)
when MATCHED then
update set num_attack = f.num_attack + 1
when not matched by target then
insert values (date_id, region_id, attack_id, target_id, gname_id, weapon_id, success, claimed, ishostkid, num_attack);