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