CODEWITHSUNDEEP
mysqlsql
Sharik Dokadia
Sharik Dokadia

Reputation: 45

SQL - Check and Update table for new 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     | 
                                     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 different (additional) rows in table secondfile. So I need a query which finds me the following:

Table secondfile
================

Emplid       | Color   | Status
-------------------------------
123          | red     |
456          | Green   |
000          | red     | added
789          | black   |
999          | white   | added

What is a good approach for such a query?

i tried this but its not working

UPDATE secondfile 
INNER JOIN firstfile 
   ON secondfile.Emplid = firstfile.Emplid
SET status = (CASE  WHEN secondfile.Emplid != firstfile.Emplid THEN 'Added' END)

Upvotes: 0

Views: 87

Answers (3)

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

You can use NOT EXISTS() and do something like this:

UPDATE secondfile     
SET status = 'Added'   
WHERE  NOT EXITS( 
                  select  1 
                  from firstfile
                  where Emplid= secondfile.Emplid
                                                   )

Upvotes: 0

Balan
Balan

Reputation: 421

Try this 

    UPDATE secondfile     
    SET status = 'Added'   
    WHERE secondfile.Emplid NOT IN( select  Emplid  from firstfile)

sample to apply case 

UPDATE secondfile 
SET status =      CASE 
                        WHEN Emplid= 10 THEN 'JustAdded'
                        WHEN Emplid= 20  THEN 'NewlyAdded' 
                        WHEN Emplid= 30 THEN 'Old' 
                        ELSE 'Added'
                    END     
WHERE secondfile.Emplid not in ( select  Emplid  from firstfile)

Upvotes: 1

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS In the following way using LEFT JOIN and IS NULL you can check the records that is not exist in the firstfile but in the second and it will update the same resultset 

UPDATE secondfile 
LEFT JOIN firstfile ON secondfile.Emplid = firstfile.Emplid
SET status = 'Added'
WHERE firstfile.Emplid IS NULL

In your code you are using secondfile.Emplid != firstfile.Emplid which will never be satisfied with INNER JOIN, this join always returns matching data only and in your case it will return data that exists in both the tables.

You can make tiny change in your query as below:

UPDATE secondfile 
LEFT JOIN firstfile ON secondfile.Emplid = firstfile.Emplid
SET status = CASE WHEN secondfile.Emplid != firstfile.Emplid THEN 'Added' ELSE status END

Upvotes: 0

Related Questions