sql - 如何向 SQL Server Merge 语句添加 where 条件以进行删除

Question

MERGE  DestinationTable AS D 
USING @SourceTable AS S
ON D.Alternate_ID = S._ID

WHEN MATCHED AND
(
       D.Candidate_ID <> S.Candidate_ID  OR  ISNULL(D.Vacancy_ID,'') <> S.Vacancy_ID   
)
THEN
UPDATE SET  
    D.FName = S.FName,
    D.Department = S.Department,

WHEN NOT MATCHED BY TARGET
THEN INSERT  
(
    Alternate_ID,
    FName,
    Department
 ) 
  VALUES 
  (
    S.ID,
    S.FName,
    S.Department  
  ) 
 WHEN NOT MATCHED BY SOURCE
 --How to add a where clause to the delete statement here
 THEN  DELETE; --E.g WHERE  D.Department <> 'HR'

如果 DestinationTable 中的记录已从源中删除，我将使用上面的 Merge 语句删除它们

有谁知道如何在 Delete 语句中添加 WHERE 条件？我只想从目标中删除 ColA 等于特定常量字符串的地方。

Answer 1

是的，请参阅 MSDN article为 MERGE .

[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
    THEN <merge_matched> ] [ ...n ]

这是适用于 NOT MATCHED BY SOURCE 的文档查询的一部分(假设 CoIA 是目标表中的一列):

...
WHEN NOT MATCHED BY SOURCE AND D.CoIA = 'YourValue' THEN
DELETE;

但是，请务必注意以下事项:

The MERGE statement can have at most two WHEN NOT MATCHED BY SOURCE clauses. If two clauses are specified, then the first clause must be accompanied by an AND < clause_search_condition > clause. For any given row, the second WHEN NOT MATCHED BY SOURCE clause is only applied if the first is not. If there are two WHEN NOT MATCHED BY SOURCE clauses, then one must specify an UPDATE action and one must specify a DELETE action. Only columns from the target table can be referenced in < clause_search_condition >.