Why MERGE for UPSERT does not work for a empty table?-CodePudding

The fact table (target) is empty (no rows). When I execute the following code it does not work for insert, only for update.

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);

Since there is no match (empty table) how to insert the data?

When there is rows and match, the command works fine.

This is only a sample code, the values are dynamically changed by ? using python and ODBC module.

CodePudding user response：

You could rearrange the query such that the source is using a values clause, aka. Table Value Constructor...

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);

CodePudding user response：

For an UPSERT you want a constructed table of values not the actual table e.g. just select the new values as shown below (or use a VALUES clause), then a row always exists which either matches or doesn't and you get an update or insert accordingly.

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);