CODEWITHSUNDEEP
mysqlsql
Sharik Dokadia
Sharik Dokadia

Reputation: 45

SQL - Compare and Update table for rows?

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);

Upvotes: 3

Views: 87

Answers (2)

user3378165
user3378165

Reputation: 6916

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;

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

I think you are actually looking for an INSERT rather than an UPDATE:

INSERT INTO secondfile (Emplid, Color, Status)
SELECT EmplId, Color, 'Removed' 
FROM firstfile AS t1
WHERE NOT EXISTS (SELECT 1
                  FROM secondfile AS t2
                  WHERE t1.Emplid = t2.Emplid AND t1.Color = t2.Color)

Upvotes: 0

Related Questions