处理删除和更新通过比较两个表

Question

我有两个表，我想在这种意义上比较，如果circuitId是相同的两列它应该检查如果CHECKSUM列是否相同，如果他们是同样也不会在表格中插入新记录。如果circiutId不在表中，它应该添加一个新行。

到目前为止，我有这个，但我不知道如何实现，以便在WHEN MATCHED THEN的逻辑里面。所以它内部检查校验和是否相同，从而插入记录？

MERGE INTO ARCHIVE.F1_CIRCUITS n 
     USING EXTRACT.F1_CIRCUITS o 
     ON 
     (n.CIRCUITID= o."circuitId") 
     WHEN MATCHED THEN 
     WHEN NOT MATCHED THEN 
      INSERT VALUES 
      ("circuitId","circuitRef","name","location","country","lat","lng","alt","url", CURRENT_TIMESTAMP, NULL, HASH_MD5("circuitId","circuitRef","name","location","country","lat","lng","alt","url"), 'C', 0) 

Answer 1

我觉得insert是足够了：

INSERT INTO ARCHIVE.F1_CIRCUITS (. . .) 
    SELECT . . . 
    FROM EXTRACT.F1_CIRCUITS o 
    WHERE NOT EXISTS (SELECT 1 
         FROM ARCHIVE.F1_CIRCUITS n 
         WHERE o.circuitid = n.circuitid AND 
          o.checksum = n.checksum 
        ); 

的. . .是不同的列名和表达式。