SQL - Compare and Update table for rows?

Question

I have two tables, for example:

Table firstfile                      Table secondfile
===============                      ================

Emplid   | Color                     Emplid       | Color   |status
-------------------                  -------------|---------|------
123      | red                       123          | red     |
456      | green                     456          | Green   |
789      | black                     000          | red     | 
777      | orange                    789          | black   |
                                     999          | white   | 

Table firstfile is my source table and secondfile is the destination table. Now I need a query which finds all the rows in firstfile that does not exist in table secondfile. So I need a query which finds me the following:

Table secondfile
================
Emplid       | Color   | Status
-------------------------------
123          | red     |
456          | Green   |
000          | red     | 
789          | black   |
999          | white   | 
777          | orange  | Removed

What is a good approach for such a query in CASE WHEN format?

I tried this but it's not working:

UPDATE second file 
set status = (CASE 
                 WHEN first file.Emplid not In (select Emplid 
                                               from secondfile) 
                    THEN 'Remove' 
              END);

Answer 1

You can not UPDATE a row that doesn't exist, you can INSERT a new row.

You can do it with the NOT IN function:

INSERT INTO secondfile
SELECT  f.Emplid,f.Color, 'Removed' 
FROM    firstfile f
WHERE   f.Emplid NOT IN (SELECT 1 FROM secondfile s WHERE f.Emplid=s.Emplid)

Or with the NOT EXISTS function:

INSERT INTO secondfile
SELECT f.Emplid,f.Color, 'Removed'
FROM firstfile f 
WHERE NOT EXISTS(SELECT s.Emplid FROM secondfile s)

You can also do it with a JOIN:

INSERT INTO secondfile
SELECT f.Emplid,f.Color, 'Removed'
FROM firstfile f 
LEFT OUTER JOIN secondfile s ON f.Emplid = s.Emplid 
WHERE s.Emplid IS NULL;