MERGE 문을 사용하면 테이블에 신규 행을 삽입하거나, 테이블의 기존 행을 갱신, 삭제할 수 있다.

 

[기본 문법]

MERGE 문의 구문.

USING 절에 지정한 소스 테이블을 INTO 절에 지정한 타깃 테이블과 ON 절의 조건으로 조인한 후, 조인이 성공하면 MERGE UPDATE 절, 조인이 실패하면 MERGE INSERT 절을 수행한다.

 

MERGE

INTO {table | view | (subquery)} [t_alias]

USING {table | view | (subquery)} [t_alias]

ON (condition)

WHEN MATCHED THEN -- MERGE UPDATE 절

UPDATE

SET column = {expr | DEFAULT} [, column = {expr | DEFAULT}] ...

[WHERE condition]

[DELETE

WHERE condition]

WHEN NOT MATCHED THEN -- MERGE INSERT 절

INSERT [(column [, column] ...)]

VALUES ({expr | DEFAULT} [, {expr | DEFAULT}]...)

[WHERE condition];

 

항목 - 설명

INTO 절 - 갱신 또는 삽입할 타깃 테이블

USING 절 - 갱신 또는 삽입에 사용할 소스 테이블

ON 절 - 갱신 또는 삽입을 결정하는 조건

MERGE UPDATE 절 - ON 절의 조건이 만족하는 경우 수행될 구문

MERGE INSERT 절 - ON 절의 조건이 만족하지 않는 경우 수행될 구문

 

-- 예제 테이블 생성

DROP TABLE T1 PURGE;

DROP TABLE T2 PURGE;

DROP TABLE T3 PURGE;

DROP TABLE T4 PURGE;

 

CREATE TABLE T2 AS SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE DEPTNO = 20;

CREATE TABLE T1 AS SELECT * FROM T2 WHERE EMPNO IN (7369, 7566);

CREATE TABLE T3 AS SELECT * FROM T2 WHERE EMPNO = 7369;

CREATE TABLE T4 AS SELECT '2050' AS YYYY, A.* FROM T2 A UNION ALL

SELECT '2051' AS YYYY, A.* FROM T2 A;

 

-- 아래는 T1, T2 테이블을 조회한 결과.

-- SMITH와 JONES는 두 테이블에 모두 존재한다.

 

SELECT * FROM T1;

SELECT * FROM T2;

 

-- 아래의 MERGE 문을 수행하면 조인에 성공한 행은 갱신되고, 실패한 행은 삽입된다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.JOB)

VALUES (S.EMPNO, S.ENAME, S.JOB);

 

-- 아래는 T1 테이블을 조회한 결과다.

-- SMITH와 JONES의 SAL이 갱신되었고, T1 테이블이 존재하지 않았던 SCOTT, ADAMS, FORD가 신규 행으로 삽입되었다.

 

SELECT * FROM T1;

 

-- 이후 예제부터 MERGE 문을 수행하고 결과를 확인한 후 롤백을 수행하도록 하자.

ROLLBACK;

 

[선택 작업]

10.1 버전부터 MERGE UPDATE 절과 MERGE INSERT 절을 선택적으로 사용할 수 있다.

 

-- 아래는 MERGE UPDATE 절만 사용한 쿼리

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

-- 아래는 T1 테이블을 조회한 결과.

-- 기존 행만 갱신된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

-- 위 MERGE 문은 아래 UPDATE 문과 결과가 동일하다.

UPDATE (SELECT A.SAL, B.SAL AS SAL_N FROM T1 A, T2 B WHERE B.EMPNO = A.EMPNO)

SET SAL = SAL_N - 500;

 

-- 아래는 MERGE INSERT 절만 사용한 쿼리

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.JOB)

VALUES (S.EMPNO, S.ENAME, S.JOB);

 

-- 아래는 T1 테이블을 조회한 결과다.

-- 기존 행은 갱신되지 않고 신규 행만 삽입된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

-- 위 MERGE 문은 아래 INSERT 문과 결과가 동일하다.

INSERT INTO T1 (EMPNO, ENAME, JOB, SAL)

SELECT EMPNO, ENAME, JOB, SAL

FROM T2 A

WHERE NOT EXISTS (SELECT 1 FROM T1 X WHERE X.EMPNO = A.EMPNO);

 

[WHERE 절]

10.1 버전부터 MERGE UPDATE 절과 MERGE INSERT 절에 WHERE 절을 사용할 수 있다.

 

아래는 MERGE UPDATE 절에 WHERE 절을 사용한 쿼리다.

WHERE 절에 타깃 테이블(T1)의 일반 조건을 기술했다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500

WHERE T.JOB = 'CLERK';

 

아래는 T1 테이블을 조회한 결과다.

JOB이 CLERK인 SMITH의 SAL만 갱신된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

아래와 같이 인라인 뷰를 사용해도 동일한 결과를 얻을 수 있다.

 

MERGE

INTO (SELECT * FROM T1 WHERE JOB = 'CLERK') T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

-- 아래 쿼리는 MERGE UPDATE 절의 WHERE 절에 소스 테이블(T2)의 일반 조건을 기술했다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500

WHERE S.JOB = 'CLERK';

 

-- 아래는 T1 테이블을 조회한 결과다.

-- JOB이 CLERK인 SMITH의 SAL만 갱신된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

-- 아래와 같이 인라인 뷰를 사용해도 동일한 결과를 얻을 수 있다.

 

MERGE

INTO T1 T

USING (SELECT * FROM T2 WHERE JOB = 'CLERK') S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

-- 아래 쿼리와 같이 조건에 소스 테이블과 타깃 테이블의 열을 함께 사용할 경우에는 MERGE UPDATE 절의 WHERE 절에 조건을 기술해야 한다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500

WHERE ( (T.JOB = 'CLERK' AND S.SAL >= 1000)

OR (T.JOB <> 'CLERK'));

 

-- 아래 쿼리는 에러가 발생한다.

-- MERGE INSERT 절의 WHERE 절에는 소스 테이블(T2)의 일반 조건만 기술할 수 있다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.JOB)

VALUES (S.EMPNO, S.ENAME, S.JOB)

WHERE T.JOB = 'CLERK';

 

ORA-38102 : INSERT WHERE 절에 부적합한 열이 있음 : "T"."JOB"

 

-- 아래 쿼리는 수행 시점에 에러가 발생할 수 있다.

-- MERGE INSERT 절을 사용할 경우 타깃 테이블 (T1)의 인라인 뷰에 일반 조건을 기술하지 않아야 한다.

(MERGE UPDATE 절과 MERGE INSERT 절을 함께 사용할 경우에도 타깃 테이블의 일반 조건을 기술하면 에러가 발생할 수 있다.)

 

MERGE

INTO (SELECT * FROM T1 WHERE JOB = 'CLERK') T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.JOB)

VALUES (S.EMPNO, S.ENAME, S.JOB);

 

ORA-00001: 무결성 제약 조건 (SCOTT.T1_PK)에 위배됩니다

 

-- 아래 쿼리는 MERGE INSERT 절의 WHERE 절에 소스 테이블 (T2)의 일반 조건을 기술했다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.JOB)

VALUES (S.EMPNO, S.ENAME, S.JOB)

WHERE S.JOB = 'CLERK';

 

-- 아래는 T1 테이블을 조회한 결과다.

-- ADAMS가 신규 행으로 삽입된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

-- 아래와 같이 인라인 뷰를 사용해도 동일한 결과를 얻을 수 있다.

-- MERGE UPDATE 절, MERGE INSERT 절의 WHERE 절보다 인라인 뷰에 일반 조건을 기술하는 편이 성능 측면에서 유리할 수 있다.

(옵티마이저 동작과 관련된 내용이다. 실행 계획 수립 시 일반 조건을 사용할 수 있는 방식에 차이가 있다는 정도로 이해하자.)

 

MERGE

INTO T1 T

USING (SELECT * FROM T2 WHERE JOB = 'CLERK') S

ON (T.EMPNO = S.EMPNO)

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.JOB)

VALUES (S.EMPNO, S.ENAME, S.JOB);

 

[DELETE 절]

10.1 버전부터 MERGE UPDATE 절에 DELETE 절을 기술할 수 있다.

DELETE 절은 MERGE UPDATE 절로 갱신된 행을 대상으로 수행되며, 갱신된 값을 기준으로 행을 삭제한다.

 

-- 아래는 MERGE UPDATE 절에 DELETE 절을 사용한 쿼리다.

-- 갱신된 SAL이 2000보다 작은 행을 삭제한다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500

WHERE T.JOB = 'CLERK'

DELETE

WHERE T.SAL < 2000;

 

-- 아래는 T1 테이블을 조회한 결과다.

갱신된 SAL이 300인 SMITH가 삭제된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

-- 아래는 MERGE 문의 모든 절을 사용한 쿼리다.

-- MERGE UPDATE 절에 의해 T1, T2 테이블에 모두 존재하는 행 중 JOB이 CLERK인 행의 SAL이 갱신되고, 갱신된 SAL이 2000보다 작은 행이 삭제된다. MERGE INSERT 절에 의해 T2 테이블에만 존재하는 행 중 JOB이 CLERK인 행이 삽입된다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500

WHERE T.JOB = 'CLERK'

DELETE

WHERE T.SAL < 2000

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.JOB)

VALUES (S.EMPNO, S.ENAME, S.JOB)

WHERE S.JOB = 'CLERK';

 

-- 아래는 T1 테이블을 조회한 결과다.

 

SELECT * FROM T1;

 

[고급 주제]

MERGE 문에 대한 고급 주제를 살펴보자. 모두 ON 절과 관련된 내용이다.

 

[조인 차수]

MERGE 문도 UPDATE 문처럼 조인 차수에 따라 에러가 발생할 수 있다.

 

-- 아래 쿼리는 에러가 발생한다.

-- T1, T4 테이블의 조인 차수가 1:M이므로 T1 테이블의 값이 T4 테이블의 값으로 여러 번 갱신될 수 있기 때문이다.

 

MERGE

INTO T1 T

USING T4 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

ORA-30926: 원본 테이블의 고정 행 집합을 가져올 수 없습니다.

 

-- 아래 쿼리는 에러가 발생하지 않는다.

-- ROW_NUMBER 함수를 사용하여 조인 차수를 1:1로 변경했다.

 

MERGE

INTO T1 T

USING (SELECT *

FROM (SELECT A.*

, ROW_NUMBER () OVER (PARTITION BY A.EMPNO

ORDER BY A.YYYY DESC) AS RN

FROM T4 A)

WHERE RN = 1) S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

[조인 조건]

ON 절에 기술된 열은 갱신할 수 없다.

무한 루프가 발생할 수 있기 때문이다.

 

-- 아래 쿼리는 에러가 발생한다.

MERGE

INTO T1 T

USING (SELECT EMPNO, EMPNO + ROW_NUMBER () OVER (ORDER BY EMPNO) AS EMPNO_N

FROM T1) S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.EMPNO = S.EMPNO_N;

 

ORA-38104: ON 절에서 참조되는 열은 업데이트할 수 없음: "T"."EMPNO"

 

-- 갱신할 테이블이 동일 테이블이면 ROWID 슈도 칼럼을 사용할 수 있다.

MERGE

INTO T1 T

USING (SELECT EMPNO + ROW_NUMBER () OVER (ORDER BY EMPNO) AS EMPNO_N FROM T1) S

ON (T.ROWID = S.ROWID)

WHEN MATCHED THEN

UPDATE

SET T.EMPNO = S.EMPNO_N;

 

-- 다른 테이블을 기준으로 ON 절에 기술된 열을 갱신하려면 USING 절에서 갱신할 테이블을 조인해야 한다.

-- 아래 쿼리는 인라인 뷰(T3)와 T1 테이블을 조인했다.

 

MERGE

INTO T1 T

USING (SELECT S.EMPNO_N, B ROWID AS RID

FROM (SELECT EMPNO, EMPNO + ROW_NUMBER () OVER (ORDER BY EMPNO) AS EMPNO_N

FROM T3) A

, T1 B

WHERE B.EMPNO = A.EMPNO) S

ON (T.ROWID = S.RID)

WHEN MATCHED THEN

UPDATE

SET T.EMPNO = S.EMPNO_N;

 

[일반 조건]

MERGE 문의 ON 절은 SELECT 문의 WHERE 절과 유사하게 동작한다.

조인 조건과 일반 조건을 기술할 수 있지만 조인 조건만 사용하는 편이 성능 측면에서 효율적이다.

(옵티마이저 동작과 관련된 내용이다. 실행 계획 수립 시 일반 조건을 사용할 수 있는 방식에 차이가 있다는 정도로 이해하자.)

 

아래 쿼리는 ON 절에 타깃 테이블(T1)의 일반 조건(T.JOB = 'CLERK')을 기술했다.

MERGE

INTO T1 T

USING T2 S

ON (T.JOB = 'CLERK' AND T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

-- 아래는 T1 테이블을 조회한 결과다.

-- ON 조건이 TRUE인 SMITH만 갱신된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

-- 아래 쿼리는 위 쿼리와 동일하게 동작한다. 아래 쿼리가 가독성 측면에서 바람직하다.

MERGE

INTO (SELECT * FROM T1 WHERE JOB = 'CLERK') T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

-- 아래 쿼리는 ON 절에 소스 테이블(T2)의 일반 조건(S.JOB = 'CLERK')을 기술했다.

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO AND S.JOB = 'CLERK')

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

-- 아래와 같이 인라인 뷰를 사용하는 편이 바람직하다.

MERGE

INTO T1 T

USING (SELECT * FROM T2 WHERE JOB = 'CLERK') S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

MERGE INSERT 문을 사용할 경우 ON 절에 타깃 테이블 (T1)의 일반 조건을 기술하면 에러가 발생할 수 있다.

아래 쿼리는 에러가 발생한다. ON 절이 FALSE인 행이 삽입되므로 T.JOB <> 'CLERK' OR <> T.EMPNO = S.EMPNO 조건이 TRUE인 행이 삽입될 수 있다.

JOB이 CLERK이 아니고 EMPNO가 동일한 행이 TRUE로 평가되므로 무결성 제약 조건 에러가 발생한 것이다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.JOB = 'CLERK' AND T.EMPNO = S.EMPNO)

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.SAL)

VALUES (S.EMPNO, S.ENAME, S.SAL);

 

ORA-00001: 무결성 제약 조건(SCOTT.T1_PK)에 위배됩니다.

 

-- ON 절에 소스 테이블 (T2)의 일반 조건을 기술해도 에러가 발생할 수 있다. 원이느 위 쿼리와 동일하다.

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO AND S.JOB = 'CLERK')

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.SAL)

VALUES (S.EMPNO, S.ENAME, S.SAL);

 

ORA-00001: 무결성 제약 조건(SCOTT.T1_PK)에 위배됩니다.

 

[아우터 조인]

ON 절의 조인 조건에 (+) 기호를 기술하면 소스 테이블과 타깃 테이블이 아우터 조인으로 조인된다.

타깃 테이블의 전체 행을 갱신할 때 사용할 수 있다.

 

아래 쿼리는 ON 절의 조인 조건에 (+) 기호를 기술했다. 타깃 테이블 (T1)을 아우터 기준으로 소스 테이블 (T3)을 아우터 조인한다.

 

MERGE

INTO T1 T

USING T3 S

ON (T.3MPNO = S.EMPNO (+))

WHEN MATCHED THEN

UPDATE

SET T.SAL = NVL (S.SAL - 500, 0);

 

-- 아래는 T1 테이블을 조회한 결과다.

-- JONES는 T3 테이블에 존재하지 않지만 아우터 조인으로 인해 SAL이 0으로 갱신되었다.

 

SELECT * FROM T1;

 

-- 아래 쿼리는 위 쿼리와 결과가 동일하다.

-- 상관 서브 쿼리를 사용했기 때문에 쿼리 성능이 저하될 수 있다.

 

UPDATE T1 A

SET SAL = NVL ((SELECT X.SAL FROM T3 X WHERE X.EMPNO = A.EMPNO) - 500, 0);

 

-- 아래와 같이 인라인 뷰를 사용하면 MERGE 문과 동일한 성능을 보장할 수 있다.

UPDATE (SELECT A.SAL, B.SAL AS SAL_N FROM T1 A, T3 B WHERE B.EMPNO (+) = A.EMPNO)

SET SAL = NVL (SAL_N - 500, 0);

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

+ Recent posts