合并插入更新删除

MERGE INTO targetTable

USING sourceTable 
ON (targetTable.PKID = sourceTable.PKID)

WHEN MATCHED AND (targetTable.PKID > 100) THEN
    DELETE

WHEN MATCHED AND (targetTable.PKID <= 100) THEN 
    UPDATE SET 
        targetTable.ColumnA = sourceTable.ColumnA, 
        targetTable.ColumnB = sourceTable.ColumnB

WHEN NOT MATCHED THEN
    INSERT (ColumnA, ColumnB) VALUES (sourceTable.ColumnA, sourceTable.ColumnB);

WHEN NOT MATCHED BY SOURCE THEN
    DELETE
; --< Required

描述:

  • MERGE INTO targetTable - 要修改的表

  • USING sourceTable - 数据源(可以是表或视图或表值函数)

  • ON ... - targetTablesourceTable 之间的连接条件。

  • WHEN MATCHED - 找到匹配项时要采取的措施

    • AND (targetTable.PKID > 100) - 为采取行动必须满足的附加条件
  • THEN DELETE - 删除 targetTable 的匹配记录

  • THEN UPDATE - 更新 SET .... 指定的匹配记录列

  • WHEN NOT MATCHED - 在 targetTable找不到匹配时要采取的行动 ****

  • WHEN NOT MATCHED BY SOURCE - 在 sourceTable找不到匹配时要采取的行动 ****

评论:

如果不需要特定的操作,则省略条件,例如删除 WHEN NOT MATCHED THEN INSERT 将阻止插入记录

合并语句需要终止分号。

限制:

  • WHEN MATCHED 不允许采取行动
  • UPDATE 动作只能更新一行。这意味着连接条件必须产生唯一匹配。