Merge In ORACLE

Merge is a combination of insertion and updation.We normally do updation when ever a match is found and do insertion when not found.Merge statement allow both in a single statement.
4 Things we need to find before start doing merge
1) To which table data should merge
2)What is the matching criteria
3)What should do when a match found
4)What should do when no match found

Suppose we have a table called Employee_2.We need to fill this table with the data from table Employee .As Employee_2 table has some data we have to check whether same employee name eist in Employee_2 table as that in Employee table.If a match in Employee name found update the Employee_2 table with the data in Employee table for that row otherwise insert the entire row into Employee_2 table.

Below shows the Employee_2 table with initial data.

Below is the Employee table from which we are going to migrate

We are going to migrate now.As of now we need records which has Dept_Id in 1 or 2.If there is a match of Emp_Name  in the employee_2 table with Employee table, update the Employee_2 table otherwise insert the record from Employee table.

Query is given below,

MERGE INTO employee_2 c
USING (SELECT emp_name,emp_age FROM employee WHERE dept_id IN(1,2)
)v
ON (v.emp_name=c.EMP_NAME)
WHEN MATCHED THEN
UPDATE SET emp_age=v.emp_age
WHEN NOT MATCHED THEN
INSERT (emp_name,emp_age) VALUES (v.emp_name,v.emp_age);

Employee_2 table after merge

Here 'akhil' was there in the Employee_2 table with Dept_id =1 so updation happened for those records and there is no records exist for Dept_Id =2.So new records inserted.

Content in the USING section is the table of records which we need to insert.A subset of theese records are insert or update into the table.So select all possible records in this section itself.