title: WHEN NOT MATCHED BY SOURCE ignores ON filter
body: <p>I have a SQL proc in a web service that accepts a collection of user data and performs the following:</p>
  
  <ol>
  <li>If the user does not exist at destination, insert it</li>
  <li>If the user exists in destination, update with any new details from source</li>
  <li>If the user does not exist in source, then archive them in destination</li>
  </ol>
  
  <p>There is a problem with the last part (<code>WHEN NOT MATCHED BY SOURCE THEN UPDATE</code>) in the sense that EVERY row in the table is updated to have <code>archived=1</code>.  I assumed that only rows with <code>customerId</code> matching that of <code>@customerId</code> would be affected, but its updating all rows except those matched in <code>ON</code> and ignoring the <code>ON</code> filter.</p>
  
  <pre><code>MERGE
      [tbl_Users] AS tgt -- Target
  USING
      #tbl_working AS src -- Source
  ON
      (tgt.customerID = @customerId) AND
      (tgt.employeeID = src.EmployeeId) 
  WHEN MATCHED THEN UPDATE SET -- user exists; update
      tgt.username = src.username
      tgt.name = src.FirstName
  WHEN NOT MATCHED BY TARGET THEN INSERT -- user does not exist; create
      (
      customerID,
      username,
      name
      ) 
      VALUES 
      (
      @customerId,
      src.UserName,
      src.Name
      )
  WHEN NOT MATCHED BY SOURCE THEN UPDATE -- user no longer required; archive
      SET tgt.archived = 1;
  </code></pre>
  
  <p>I might not be using the last part correctly, so would appreciate any input please.</p>