How to update mysql record with comparing multiple columns

Question

Actually I need to update the mysql records when comparing two mysql tables with multiple columns, if the record doesn't exist, it should insert as a new record. How can i achieve it? Here is my scenario below. Thanks in advance

Table 1 :

Name   | Age   | occupation    | DOJ    | Salary   |
----------------------------------------------------------------
Raju  | 27 | Manager | 12/12/12 | 12,000
--------------------------------------
Raman  | 30 | Director | 11/11/11 | 14,000
-
Sriram  | 25 | Assistant | 10/10/10 | 10,000
-

Table 2 :

Name   | Age   | occupation    | DOJ    | Salary   |
----------------------------------------------------------------
Raju  | 27 | Manager | 12/12/12 | 12,000
--------------------------------------
Raman  | 30 | Director | 8/8/8 | 18,000
-
Ravi  | 34 | CEO | 9/9/9 | 30,000
-

I have a excel with bunch of records with the above structure, I need to upload the records in a temporary table(temp_table), then compare that table with the Main table(main_table) whether anyone records are exists or not, if exists with any updates, need to do that update on the Main_table, otherwise insert it as a new one in main_table.

Thank you.

You will need to create a unique index in table1 and table2 on name,age,occupation,doj and salary. Then you can do `insert ignore into table1 select * from table2` all day. Or 'replace into table2 select * from table1` if you want to update things. — Dimi, Mar 22 '17 at 14:15
Your question isnt clear. Where you will insert?, where will you update?Why Raman table1 is different of Raman table2? Show us db schema, sample data and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. — Juan Carlos Oropeza, Mar 22 '17 at 14:20
Possible duplicate of [Insert into a MySQL table or update if exists](http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists) — Masivuye Cokile, Mar 22 '17 at 14:22
@JuanCarlosOropeza I have explained enough information like the structure of the table as you asked about the schema, sample data also. If you are asking why the table 1 "Raman" is different from table 2 "Raman", thats is my need to update it. For example, Do you want me to explain about the incident happened or about the day which the incident happened? — Anandharaman Buvaneswaran, Mar 22 '17 at 14:59
I told you for me the explanation is very poor. Remember I don't know what is your problem. That is why I suggest those link to help you make better questions, You are the interested party, you should do the effort to explain the problem so every one can understand it. Not sure if you try to merge two table, try to do dual insert on both table so I wont waste my time trying to guess what you mean. — Juan Carlos Oropeza, Mar 22 '17 at 15:03

score 1 · Answer 1 · answered Mar 22 '17 at 15:03

You can write a selection which will return 1 if and only if a given Name exists. Then you can use this as a sub-query for another selection which will negate the existence, to find what should be inserted. Finally, you can use that second selection for insert-select:

insert into Table2(Name, Age, Occupation, Doj, Salary)
select Name, Age, Occupation, Doj, Salary
from Table1 t2 where not exists (select 1
                              from Table2 t
                              where t.Name = t2.Name)

How to update mysql record with comparing multiple columns

1 Answers1