title: How to adding a where condition to SQL Server Merge statement for Deletes
body: <pre><code>MERGE  DestinationTable AS D 
  USING @SourceTable AS S
  ON D.Alternate_ID = S._ID
  
  WHEN MATCHED AND
  (
         D.Candidate_ID &lt;&gt; S.Candidate_ID  OR  ISNULL(D.Vacancy_ID,'') &lt;&gt; 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 &lt;&gt; 'HR'
  </code></pre>
  
  <p>I'm using the Merge Statement above to delete records in the DestinationTable if they have been removed from source</p>
  
  <p>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.</p>