SQL:如果不存在插入，则更新

Question

我知道有很多关于这个主题的帖子，但这有点不同。我有两个桌子variant_detail (vad)和variant_external_analysis (vaea)。vaea主要是空的，我需要为vad中的所有行更新它。如果我在vaea中插入，并且该行已经存在，则它会复制该行，从而破坏数据库。

在1行测试中，我解决了以下问题：

IF NOT EXISTS (SELECT vaea_vad_id FROM variant_external_analysis 
            JOIN variant_detail ON vad_id = vaea_vad_id 
            WHERE vad_variant_code = 16469)
BEGIN
    INSERT variant_external_analysis (vaea_vad_id) 
    SELECT vad_id
    FROM variant_detail
END

UPDATE variant_external_analysis

SET vaea_last_amended_on = GETDATE(), vaea_last_amended_by = 13, vaea_n_1 = 1

FROM variant_external_analysis
JOIN variant_detail ON vad_id = vaea_vad_id

WHERE vad_variant_code = 16469

在我移除WHERE语句以使其影响整个表之前，这是非常有效的。下面的代码正确地更新，但现在不再插入

IF NOT EXISTS (SELECT vaea_vad_id FROM variant_external_analysis

JOIN variant_detail ON vad_id = vaea_vad_id 

            WHERE vad_id = vaea_vad_id)
BEGIN
    INSERT variant_external_analysis (vaea_vad_id) 
    SELECT vad_id
    FROM variant_detail
END

UPDATE variant_external_analysis

SET vaea_last_amended_on = GETDATE(), vaea_last_amended_by = 13, vaea_n_1 = 1

FROM variant_external_analysis
JOIN variant_detail ON vad_id = vaea_vad_id

我可能错过了一些简单的东西。

Answer 1

代码看起来非常类似于The，下面是一个如何在MS中使用合并实现它的示例：

merge variant_external_analysis vea
--using here left join (along with where condition) we'll get only rows 
--that are in variand_details but aren't in variant_external_analysis
using(select vd.vad_id 
  from variant_detail vd
  left join variant_external_analysis vaea on vd.vad_id = vaea.vaea_vad_id 
  where vaea.id is null) t --or any another column of vaea
on (vea.vaea_vad_id = t.vad_id)
when not matched then 
    insert (vaea_vad_id, vaea_last_amended_on, vaea_last_amended_by, vaea_n_1)
    values(t.vad_id, getdate(), 13, 1)
;

使用单行测试子句的UPD：

1. 在code子句中添加using作为返回列
2. 使用when条件扩展code子句

merge variant_external_analysis vea
    --using here left join (along with where condition) we'll get only rows 
    --that are in variand_details but aren't in variant_external_analysis
    using(select vd.vad_id, vd.vad_variant_code 
      from variant_detail vd
      left join variant_external_analysis vaea on vd.vad_id = vaea.vaea_vad_id 
      where vaea.id is null) t --or any another column of vaea
    on (vea.vaea_vad_id = t.vad_id)
    when not matched and t.vad_variant_code = 16469 then 
        insert (vaea_vad_id, vaea_last_amended_on, vaea_last_amended_by, vaea_n_1)
        values(t.vad_id, getdate(), 13, 1)
    ;

Answer 2

在where子句中有"vad_variant_code = vad_id = vaea_vad_id“。