Merge

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.

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	#