How to adding a where condition to SQL Server Merge statement for Deletes

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'

I'm using the Merge Statement above to delete records in the DestinationTable if they have been removed from source

Does any one know how a can add a WHERE condition to the Delete statement?. I want to only delete from destination where ColA is equal to a particular constant string.

Answer 1

Yes, see the MSDN article for MERGE.

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

Here is the documentation applied to the NOT MATCHED BY SOURCE portion of your query (assuming CoIA is a column in the destination table):

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

However, be sure to note the following:

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 >.