title: Update with the Case When Exists clause
body: <p>I have two tables. I need to update one column in one table with '1' and '0'. Having '1' where E_ID are matching in both columns and '0' where E_ID does not exists in the second table.<br>
  Note: One table has all E_IDs, second table has a subset of E_IDs. None on the E_ID columns in both tales contain NULL. </p>
  
  <p>The query below does only the first part of of the job (updates to '1'). </p>
  
  <pre><code>UPDATE [MIDASFactory].[dbo].[Employees] 
  SET  [Changed To Residential] = 1
  FROM  ##formerEmployees t
  INNER JOIN [dbo].[Employees] (NOLOCK)
       ON t.e_ID = E_ID
  </code></pre>
  
  <p>I modified the query above to also update with '1' and '0'as described above.</p>
  
  <p>But it does not work correctly as it updates all rows to the first value 
  returned by the select sub-clause in the query below where E_ID is matching in both tables. </p>
  
  <pre><code>UPDATE [dbo].[Employees] 
  SET  [Current Employee] = 
      CASE WHEN EXISTS (SELECT * FROM [dbo].[Employees] P (NOLOCK) 
                        LEFT OUTER JOIN  ##formerEmployees t (NOLOCK) ON P.E_ID=t.e_ID)
              THEN 1 
              ELSE 0
          END 
  FROM  ##formerEmployees t
  </code></pre>