Update with the Case When Exists clause

Question

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

The query below does only the first part of of the job (updates to '1').

UPDATE [MIDASFactory].[dbo].[Employees] 
SET  [Changed To Residential] = 1
FROM  ##formerEmployees t
INNER JOIN [dbo].[Employees] (NOLOCK)
     ON t.e_ID = E_ID

I modified the query above to also update with '1' and '0'as described above.

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.

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

Answer 1

You have not correlated your subquery to your UPDATE query (Employees). You probably thought you were doing that with your JOIN, but in fact you were just creating a second, separate reference to Employees, which does not correlate to your outer query.

This should work:

UPDATE e
SET  [Current Employee] = 
    CASE WHEN EXISTS (SELECT * FROM ##formerEmployees t (NOLOCK) WHERE e.E_ID=t.e_ID)
            THEN 1 
            ELSE 0
        END 
FROM  [dbo].[Employees] e