Merge

From Oracle FAQ
Jump to: navigation, search

MERGE (sometimes also called UPSERT) is a SQL command that performs a series of conditional update and/or insert operations. A row is updated if it exists, inserted if it doesn't. Merge was first introduced with Oracle 9i.

[edit] Examples

Add rows or update rows and possibly delete, from in emp based on data in emp_load:

MERGE INTO emp e1 USING emp_load e2 ON (e2.empno = e1.empno)
WHEN MATCHED THEN 
  UPDATE SET e1.sal = e2.sal DELETE WHERE sal <= 0
WHEN NOT MATCHED THEN
  INSERT (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  VALUES (e2.empno, e2.ename, e2.job, e2.mgr, e2.hiredate, e2.sal, e2.comm, e2.deptno);

Note that the MATCHED and NOT MATCHED clauses are optional (since 10g) making it possible to only perform the INSERT or UPDATE portions. In addition, from 10g, a DELETE clause can be added to the MATCHED case (see example above); the DELETE WHERE clause applies on values existing after the execution of the UPDATE SET clause.

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #