Merge

Merge는
Update와 Insert를 합친 것이라고 볼 수 있다. 그래서 때로는 Upsert라고 부르기도 한다.
9i부터 등장

다음과 같이 t1테이블과 t2 테이블이 있다고 가정하자.

select * from t1;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	80/12/17	800		20
7499	ALLEN	SALESMAN	7698	81/02/20	1600	300	30
7521	WARD	SALESMAN	7698	81/02/22	1250	500	30
7566	JONES	MANAGER	7839	81/04/02	2975		20
7654	MARTIN	SALESMAN	7698	81/09/28	1250	1400	30
7698	BLAKE	MANAGER	7839	81/05/01	2850		30
7782	CLARK	MANAGER	7839	81/06/09	2450		10
7788	SCOTT	ANALYST	7566	82/12/09	3000		20

select * from t2;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	80/12/17	800		20
7499	ALLEN	SALESMAN	7698	81/02/20	1600	300	30
7521	WARD	SALESMAN	7698	81/02/22	1250	500	30
7566	JONES	MANAGER	7839	81/04/02	2975		20
7654	MARTIN	SALESMAN	7698	81/09/28	1250	1400	30
7698	BLAKE	MANAGER	7839	81/05/01	2850		30
7782	CLARK	MANAGER	7839	81/06/09	2450		10
7788	SCOTT	ANALYST	7566	82/12/09	3000		20
7839	KING	PRESIDENT		81/11/17	5000		10
7844	TURNER	SALESMAN	7698	81/09/08	1500	0	30
7876	ADAMS	CLERK	7788	83/01/12	1100		20
7900	JAMES	CLERK	7698	81/12/03	950		30
7902	FORD	ANALYST	7566	81/12/03	3000		20
7934	MILLER	CLERK	7782	82/01/23	1300		10
4444					4444

merge 명령을 실행한다.
t1테이블의 empno와 t2테이블의 empno가 같으면 update를 하고 다를 경우 insert를 하는 것이다.

MERGE INTO t1 a
USING           t2 b                                                   -- 재료가 되는 테이블, 서브 쿼리도 들어올 수 있음.
ON (a.empno = b.empno)
WHEN MATCHED THEN
    UPDATE SET a.sal = b.sal, 
                        a.comm = b.comm
WHEN NOT MATCHED THEN
    INSERT (a.empno, a.ename, a.sal, a.job, a.deptno)
    VALUES (b.empno, b.ename, b.sal, b.job, b.deptno);

결과가 다음과 같이 바뀌었다.

select * from t1;

 

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	80/12/17	900		20
7499	ALLEN	SALESMAN	7698	81/02/20	1700	300	30
7521	WARD	SALESMAN	7698	81/02/22	1350	500	30
7566	JONES	MANAGER	7839	81/04/02	3075		20
7654	MARTIN	SALESMAN	7698	81/09/28	1350	1400	30
7698	BLAKE	MANAGER	7839	81/05/01	2950		30
7782	CLARK	MANAGER	7839	81/06/09	2550		10
7788	SCOTT	ANALYST	7566	82/12/09	3100		20
7844	TURNER	SALESMAN			1500		30
7839	KING	PRESIDENT			5000		10
4444					4444
7934	MILLER	CLERK			1300		10
7902	FORD	ANALYST			3000		20
7876	ADAMS	CLERK			1100		20
7900	JAMES	CLERK			950		30