MERGE Statement
Use the MERGE statement to select rows from one table for update or insertion into another table. The decision whether to update or insert into the target table is based on a condition in the ON clause. It is a new feature of Oracle Ver. 9i.
It is also known as UPSERT i.e. combination of UPDATE and INSERT.
The MERGE statement was introduced in Oracle 9i to conditionally insert or update data depending on its presence, a process also known as an "upsert". The MERGE statement reduces table scans and can perform the operation in parallel if required.
MERGE Statement Enhancements in Oracle Database 10g
Syntax
Consider the following example where data from the HR_RECORDS table is merged into the EMPLOYEES table.
MERGE INTO employees e
USING hr_records h
ON (e.id = h.emp_id)
WHEN MATCHED THEN
UPDATE SET e.address = h.address
WHEN NOT MATCHED THEN
INSERT (id, address)
VALUES (h.emp_id, h.address);
The source can also be a query.
MERGE INTO employees e
USING (SELECT * FROM hr_records WHERE job='MANAGER') h
ON (e.id = h.emp_id)
WHEN MATCHED THEN
UPDATE SET e.address = h.address
WHEN NOT MATCHED THEN
INSERT (id, address)
VALUES (h.emp_id, h.address)
One More example:
MERGE INTO emp
USING new_emp src
ON (emp.empno = src.empno)
WHEN MATCHED
THEN
UPDATE SET sal = src.sal, job = src.job, deptno = src.deptno
DELETE
WHERE job = 'Resigned'
WHEN NOT MATCHED
THEN
INSERT (empno,
sal,
ename,
job,
deptno)
VALUES (src.empno,
src.sal,
src.ename,
src.job,
src.deptno)
new records in NEW_EMP represent newly hired employees; these should be created in the EMP table records in NEW_EMP with a corresponding (matched by EMPNO) record in EMP represented changed Employees records – new job, salary or deptno; the EMP records should be updated from their matches in NEW_EMP
finally, we have a special requirement – well, actually two:
when an Employees was resigned, he will be in the NEW_EMP table with his or her job set to Resigned ; the corresponding record in the EMP table should be removed
We also have records in the EMP table for temporary employees (TEMPs); their Job is set to TEMP; the temporary records do not get fed from the external systems, they will never appear in NEW_EMP. Whenever we refresh the EMP table from NEW_EMP, we want all TEMPs to be removed from the EMP table
Watch out for these when you MERGE
You cannot update any of the columns you are merging on. If you try updating a student’s id in the example above, this error will show up in 10G:
ORA-38104: Columns referenced in the ON Clause cannot be updated
MERGE is a deterministic statement – that is, you cannot update a row of the target table multiple times in the same MERGE statement.
You must have the INSERT and UPDATE privileges on the target table and the SELECT privilege on the source table. To specify the DELETE clause, you must also have the DELETE privilege on the target table.
When using MERGE for the DELETE operation, remember that:
DELETE checks the match condition on the target table, not the source.
DELETE works only on rows updated during MERGE. Any rows in the target table that are not processed during MERGE are not deleted, even if they match the DELETE condition.
