[oracle, sql] 04 | MERGE문

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으로 갱신될 것이다.

입력

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

    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 d.employee_id = 161’ 이라고 명시하면 161 사원은 삭제된다.

입력

    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
          DELETE WHERE (B.employee_id = 161)
     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 ex5_3
     ORDER BY employee_id;

결과

    EMPLOYEE_ID  BONUS_AMT
    ------------ ----------
            148          0
            153          0
            154          0
            155          0
            160        7.5

[출처] https://thebook.io/006696/part01/ch03/04/