[oracle] merge문

이미 존재하는 값은 update
신규로 입력된 값은 insert

일명 upsert 

 

 

MERGE INTO CUSTOMER C
USING
(
      SELECT USERNO
           , USERNAME
           , ADDRESS
           , PHONE
       FROM NEW_JOIN
      WHERE INPUT_DATE = '20170724'
) N
ON ( C.USERNO = N.USERNO)
WHEN MATCHED THEN
UPDATE
SET C.USERNAME = N.USERNAME
  , C.ADDRESS  = N.ADDRESS
  , C.PHONE    = N.PHONE
WHEN NOT MATCHED THEN
INSERT ( USERNO
       , USERNAME
       , ADDRESS
       , PHONE
       )
 VALUES (
         N.USERNO
       , N.USERNAME
       , N.ADDRESS
       , N.PHONE
 )

 

MERGE INTO 테이블이름 C = INSERT 또는 UPDATE 할 테이블과 테이블의 ALIAS를 지정한다.

USING = 원하는 결과를 추출하기 위한 SELECT문. 이 SELECT문에서 나온 결과를 INSERT 또는 UPDATE 함.

ON = SELECT 한 결과와 입력하고 싶은 테이블의 UNIQUE 한 값을 매칭하는 연결고리. (주로 KEY값 사용)

WHEN MATCHED THEN = SELECT 결과가 INSERT 할 테이블에 값이 이미 존재하는 경우 UPDATE 실행.

WHEN NOT MATCHED THEN = SELECT의 결과가 INSERT 할 테이블에 값이 없는 경우 INSERT를 실행.

 

 

---

[MERGE UPDATE 예제]

MERGE INTO CUSTOMER C
USING
(
      SELECT USERNO
           , USERNAME
           , ADDRESS
           , PHONE
       FROM NEW_JOIN
      WHERE INPUT_DATE = '20170724'
) N
ON ( C.USERNO = N.USERNO)
WHEN MATCHED THEN
UPDATE
SET C.USERNAME = N.USERNAME
  , C.ADDRESS  = N.ADDRESS
  , C.PHONE    = N.PHONE

 

* MERGE 문은 DML 의 문장으로 트랜잭션(TRANSACTION) 단위로 실행되기 때문에 COMMIT, ROLLBACK 을 처리해야 완전히 반영.