Merge

 The Merge Statement, in Oracle 9i, merges two tables,i.e., inserts or updates the rows in the target table using a base table, based on the condition(s) specified. The below example uses two tables emp_src (source) and emp_tgt (target). The structure of both the tables are similar

DESCRIBE emp_tgt

Name Null Type

--------- -------- ------------

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)

SAL NUMBER(7,2)

COMM NUMBER(7,2)

MGR NUMBER(4)

DEPTNO NUMBER(2)

The Merge statement that merges the two tables is,

MERGE INTO emp_tgt

USING emp_src

ON (emp_tgt.empno = emp_src.empno)

WHEN MATCHED THEN

UPDATE SET

emp_tgt.ename = emp_src.ename,

emp_tgt.sal = emp_src.sal,

emp_tgt.comm = emp_src.comm,

emp_tgt.mgr = emp_src.mgr,

emp_tgt.deptno = emp_src.deptno

WHEN NOT MATCHED THEN

INSERT

(emp_tgt.empno, emp_tgt.ename, emp_tgt.sal,

emp_tgt.comm, emp_tgt.mgr, emp_tgt.deptno)

VALUES

(emp_src.empno, emp_src.ename,emp_src.sal,

emp_src.comm, emp_src.mgr,emp_src.deptno);

in which, the condition specified in the ON clause, if satisfied, then, the statement under 'WHEN MATCHED' is executed, else, the statement under 'WHEN NOT MATCHED' is executed. Here, note the syntax of the Update and Insert statements used. As seen above, this single Merge statement, does either an insert or an update on a particular table wrt a base table, based on the specified condition(s).