04 | MERGE문
MERGE문은 조건을 비교해서 테이블에 해당 조건에 맞는 데이터가 없으면 INSERT, 있으면 UPDATE를 수행하는 문장이다. 특정 조건에 따라 어떤 때는 INSERT를, 또 다른 경우에는 UPDATE문을 수행해야 할 때, 과거에는 해당 조건을 처리하는 로직을 별도로 작성해야 했지만, MERGE문이 나온 덕분에 이제 한 문장으로 처리할 수 있게 되었다.
MERGE INTO [스키마.]테이블명
USING (update나 insert될 데이터 원천)
ON (update될 조건)
WHEN MATCHED THEN
SET 컬럼1 = 값1, 컬럼2 = 값2, ...
WHERE update 조건
DELETE WHERE update_delete 조건
WHEN NOT MATCHED THEN
INSERT (컬럼1, 컬럼2, ...) VALUES (값1, 값2,...)
WHERE insert 조건;
MERGE문은 INSERT나 UPDATE에 비해 다소 복잡한데, 예제를 통해 동작 원리를 알아 보자.
입력
CREATE TABLE ex3_3 (
employee_id NUMBER,
bonus_amt NUMBER DEFAULT 0);
결과
table EX3_3이(가) 생성되었습니다.
입력
INSERT INTO ex3_3 (employee_id)
SELECT e.employee_id
FROM employees e, sales s
WHERE e.employee_id = s.employee_id
AND s.SALES_MONTH BETWEEN '200010' AND '200012'
GROUP BY e.employee_id;
결과
5개 행 이(가) 삽입되었습니다.
입력
SELECT *
FROM ex3_3
ORDER BY employee_id;
결과
EMPLOYEE_ID BONUS_AMT
----------- ---------
148 0
153 0
154 0
155 0
161 0
ex3_3이라는 신규 테이블을 생성한 뒤, sales 테이블에서 2000년 10월부터 2000년 12월까지 매출을 달성한 사원번호를 입력했다(INSERT 구문에 GROUP BY절을 추가해 사원번호의 중복을 제거했다. GROUP BY에 대해서는 이후 장에서 자세히 설명한다).
이제 사원 테이블을 검색해 ① 관리자 사번(manager_id)이 146번인 사원을 찾아 ② ex3_3 테이블에 있는 사원의 사번과 일치하면 보너스 금액(bonus_amt)에 자신의 급여(salary)의 1%를 보너스로 갱신하고, ③ ex3_3 테이블에 있는 사원의 사번과 일치하지 않으면 ①의 결과의 사원을 신규로 입력(이때 보너스 금액은 급여의 0.1%로 한다)하는데, 이때 급여가 8000 미만인 사원만 처리해 보자. 로직이 좀 복잡하니 일단 최종 답을 직접 계산해 보고 MERGE문을 작성한 후 실행해 두 결과를 비교해보자.
입력
SELECT employee_id, manager_id, salary, salary * 0.01
FROM employees
WHERE employee_id IN ( SELECT employee_id
FROM ex3_3 );
결과
EMPLOYEE_ID MANAGER_ID SALARY SALARY*0.01
----------- ----------- --------- ------------
153 145 8000 80
155 145 7000 70
148 100 11000 110
161 146 7000 70
154 145 7500 75
이 문장은 ex3_3 테이블에 있는 사원의 사번, 관리자 사번, 급여, 그리고 급여*0.01을 사원 테이블에서 조회한 것이다.
관리자 사번이 146인 사원은 161번 사원 한 명이므로 ex3_3 테이블에서 사번이 161인 건의 보너스 금액은 7,000 * 0.01, 즉 70으로 갱신될 것이다.
입력
SELECT employee_id, manager_id, salary, salary * 0.001
FROM employees
WHERE employee_id NOT IN ( SELECT employee_id
FROM ex3_3 )
AND manager_id = 146;
결과
EMPLOYEE_ID MANAGER_ID SALARY SALARY*0.001
----------- ----------- --------- -------------
159 146 8000 8
156 146 10000 10
157 146 9500 9.5
160 146 7500 7.5
158 146 9000 9
이번 쿼리 결과는 사원 테이블에서 관리자 사번이 146인 것 중 ex3_3 테이블에 없는 사원의 사번, 관리자 사번, 급여, 급여*0.001(0.1%)을 조회한 것이다. 이중 급여가 8000 미만은 160번 사원 한 명이므로 ex3_3 테이블의 160번 사원의 보너스 금액은 7.5로 신규 입력될 것이다. 그럼 이 내용을 처리하는 MERGE문을 작성해 실행해 보자.
입력
MERGE INTO ex3_3 d
① USING (SELECT employee_id, salary, manager_id FROM employees WHERE manager_id = 146) b
ON (d.employee_id = b.employee_id)
② WHEN MATCHED THEN UPDATE SET d.bonus_amt = d.bonus_amt + b.salary * 0.01
③ WHEN NOT MATCHED THEN INSERT (d.employee_id, d.bonus_amt) VALUES (b.employee_id, b.salary *.001) WHERE (b.salary < 8000);
결과
2개 행 이(가) 병합되었습니다.
입력
SELECT *
FROM ex3_3
ORDER BY employee_id;
결과
EMPLOYEE_ID BONUS_AMT
------------ ---------
148 0
153 0
154 0
155 0
160 7.5
161 70
160번 사원은 7.5로 신규 입력되었고 관리자 사번이 146번이었던 161번 사원은 70으로 보너스 금액이 갱신되었음을 확인할 수 있다. 가장 기본적인 MERGE문에 대해 알아봤는데, 이런 유형 외에도 UPDATE절에 ‘DELETE WHERE’ 구문을 추가할 수 있다. 이 구문은 UPDATE될 값을 평가해서 조건에 맞는 데이터를 삭제하는 역할을 한다. 위 MERGE문에서는 161번 사원의 보너스 금액을UPDATE 했는데, 만약 ‘DELETE WHERE