[Oracle] 오라클 MERGE INTO 개념 및 사용 방법 / UPDATE, INSERT 동시에 수행 (UPSERT)

처리할 데이터 양이 많은데 UPDATE와 INSERT를 구분해서 수행해야 하는 경우

UPDATE와 INSERT를 한 번에 수행할 수 있는 MERGE INTO 에 대해 알려 드리겠습니다.

 

MERGE INTO 기본 사용 방법

MERGE INTO 기본 사용 방법

단일 테이블 또는 조건 사용 (DUAL)

MERGE INTO DEPT A
    USING DUAL
        ON (A.DEPTNO = '50')
    WHEN MATCHED THEN
        UPDATE
            SET A.DNAME = 'PRODUCTIONS'
              , A.LOCATION = 'BUSAN'
    WHEN NOT MATCHED THEN
        INSERT (A.DEPTNO, A.DNAME, A.LOCATION)
        VALUES ('50', 'PRODUCTIONS', 'BUSAN')

 

• 단순 값을 INSERT 할때 PK 중복 오류를 피하고 싶은 경우 많이 사용
• ON 절에 PK값을 지정해서 일치 여부를 확인하고 있으면 UPDATE 또는 DELETE 없으면 INSERT 를 수행
• MATCHED / NOT MATCHED 는 필수가 아닌 선택적 사용 가능

-- UPDATE만 수행
MERGE INTO DEPT A
    USING DUAL
        ON (A.DEPTNO = '50')
    WHEN MATCHED THEN
        UPDATE
            SET A.DNAME = 'PRODUCTIONS'

--INSERT만 수행
MERGE INTO DEPT A
    USING DUAL
        ON (A.DEPTNO = '50')
    WHEN NOT MATCHED THEN
        INSERT (A.DEPTNO, A.DNAME, A.LOCATION)
        VALUES ('50', 'PRODUCTIONS', 'BUSAN')

 

다른 테이블과 조인

MERGE INTO SAL_HISTORY A
    USING EMP B
        ON (A.EMPNO = '7900' AND A.EMPNO = B.EMPNO)
    WHEN MATCHED THEN
        UPDATE
            SET A.SAL = B.SAL
    WHEN NOT MATCHED THEN
        INSERT (A.EMPNO, A.SAL)
        VALUES (B.EMPNO, B.SAL)

• 테이블에서 값을 가져와서 UPDATE 할 때 매칭이 되지 않으면 누락되는 경우 사용
• 매칭되지 않은 값을 INSERT 할지 말지 선택 가능 (MATCHED / NOT MATCHED 조건은 필수가 아닌 선택)

WHERE 절 사용 (조인이 아닌 단순 조건)

MERGE INTO DEPT A
    USING DUAL
        ON (A.DEPTNO = '50')
    WHEN MATCHED THEN
        UPDATE
            SET A.DNAME = 'PRODUCTIONS'
          WHERE A.LOCATION = 'SEOUL'

• ON 절에서 조건 일치 (MATCHED) 시 UPDATE, DELETE 문에는 WHERE 절 사용 가능
• ON 절에서 매칭된 데이터(1차 조건)에서 WHERE 절로 조건(2차 조건)을 부여
• ON 절에서 매칭되지 않은 데이터만 INSERT 실시, WHERE 절에서 조건 부합하지 않은 데이터는 변화 없음
• INSERT 문에서는 WHERE 절 사용 불가능

DELETE 문 사용

MERGE INTO DEPT A
    USING DUAL
        ON (A.DEPTNO = '50')
    WHEN MATCHED THEN
        UPDATE
            SET A.DNAME = 'PRODUCTIONS'
          WHERE A.LOCATION = 'SEOUL'
        DELETE
          WHERE A.LOCATION IN ('NEW YORK','BOSTON')

• ON 절에서 조건 일치한 값만 DELETE 적용 가능 (MATCHED)
• UPDATE 와 마찬가지로 WHERE 절 사용 가능
• UPDATE 와 동시에 DELETE 사용 가능 (UPDATE가 선행 되어야만 DELETE 사용 가능)

서브쿼리 사용

MERGE INTO SAL_HISTORY A
    USING (
    SELECT Z.EMPNO
         , Z.SAL
        FROM EMP Z
           , DEPT Y
       WHERE Z.DEPTNO = Y.DEPTNO
         AND Z.EMPNO = '7900'
    ) B
        ON (A.EMPNO = B.EMPNO)
    WHEN MATCHED THEN
        UPDATE
            SET A.SAL = B.SAL
              , A.EMPNO = B.EMPNO
    WHEN NOT MATCHED THEN
        INSERT (A.EMPNO, A.SAL)
        VALUES (B.EMPNO, B.SAL)

• 단순 UPDATE, DELETE, INSERT 사용 시 서브 쿼리 사용이 어려울 때 MERGE INTO 를 활용하여 쉽게 해결 가능
• 운영서버에서 실시간으로 마이그레이션을 수행하는 경우 활용

MERGE INTO 사용 시 주의 사항 1

MERGE INTO 조건절 UPDATE

• ON 절에서 사용한 조건은 UPDATE 불가 ( PK값을 ON절에 사용하여 최대한 조건절 조인 간소화 )

MERGE INTO 사용 시 주의사항 2

ON 절 중복 조인

• 서브쿼리 사용 시 SELECT 된 값과 원본 테이블의 값이 조인 시 중복되는 경우 오류 발생
• 서브쿼리 값이 동시에 같은 원본 행을 바라보게 되어 생기는 문제
• 조인 조건에 사용되는 컬럼에 중복 값이 있는지 점검 필요

ORA-30926 발생 원인