title: Can I apply a WHERE clause on the target in a MERGE statement?
body: <p>I have a target table containing a items that have an <code>IsActive</code> flag, and I am inserting and updating from a source table using a <code>MERGE</code> statement. If something exists in the source table then it's active, and if it doesn't then it's not active. The logic is pretty simple:</p>
  
  <ul>
  <li>if it exists in the source and the target the row should have <code>IsActive</code> true</li>
  <li>if it exists only in the source then a new row should be inserted to the target, with <code>IsActive</code> true</li>
  <li>if it exists only in the target then <code>IsActive</code> should be set to false.</li>
  </ul>
  
  <p>All very straightforward, except the target table also has a discriminating column <code>SourceId</code> which relates to the source table. So for a given source table, I only want to <code>MERGE</code> against rows with the corresponding <code>SourceId</code>.</p>
  
  <p>(My normalised table contains rows of identical data types from multiple systems - I retrieve the data from those systems individually and thus the need to merge from one source at a time)</p>
  
  <p>Here's an example:</p>
  
  <pre><code>IF OBJECT_ID('tempdb..#target') IS NOT NULL DROP TABLE #target    
  IF OBJECT_ID('tempdb..#source') IS NOT NULL DROP TABLE #source
  
  CREATE TABLE #target  ( Id INT, SourceId INT, IsActive BIT )   
  INSERT #target VALUES (1, 1, 0)
  INSERT #target VALUES (2, 1, 1)
  INSERT #target VALUES (3, 2, 1)
  
  CREATE TABLE #source ( Id INT )    
  INSERT #source VALUES (1)
  INSERT #source VALUES (4)
  
  DECLARE @SourceId INT = 1;    
  SELECT * FROM #target
  
  MERGE INTO #target t
  USING
  (
      SELECT [Id] FROM #source
  ) AS s
  ON t.[Id] = s.[Id] AND t.[SourceId] = @SourceId
  WHEN MATCHED THEN UPDATE SET [IsActive] = 1
  WHEN NOT MATCHED BY TARGET THEN INSERT VALUES ([Id], @SourceId, 1)
  WHEN NOT MATCHED BY SOURCE THEN UPDATE SET [IsActive] = 0;
  
  SELECT * FROM #target
  </code></pre>
  
  <p>My initial attempt was to include the <code>AND t.[SourceId] = @SourceId</code> in the merge condition, but obviously that won't work - it's restricting the items to merge, but not the target table. The target row ID = 3 won't match, and so it will be set to inactive, whether or not that additional condition is included.</p>
  
  <p>The end result is that whenever the procedure is run for a source system, all other systems will be set to inactive.</p>
  
  <p>My solution so far is to run the <code>MERGE</code> only for <code>MATCHED</code> and <code>NOT MATCHED BY TARGET</code>, and then run a subsequent <code>UPDATE</code> for the unmatched rows </p>
  
  <pre><code>UPDATE #target
  SET [IsEnabled] = 0 
  WHERE [SourceId] = @SourceId
  AND [ID] NOT IN (SELECT [ID] FROM #source)
  </code></pre>
  
  <p>Is there any way to include this filter condition in the <code>MERGE</code> statement? Are there any other clever ways to achieve this?</p>