[ORACLE] MERGE INTO

 
 
/**************************/
MERGE 란 무엇인가?
 
충돌나지 않게 합친다는 개념.
 
SVN에서 MERGE는 소스들의 충돌을 방지하고 적절하게 누락되지 않게 통합하기 위해 사용함.
오라클에서 MERGE 또한 같은 개념이다. TABLE에 존재 하지 않는 데이터는
그대로 변경만 하고 없는 데이터는 삽입을 하여 적절하게 통합하기 위한 예약어이다.
즉, 대상 테이블의 해당 KEY에 맞는 데이터가 이미 있으면 UPDATE, 존재하지 않으면 INSERT를 하여,
ROW 가충돌나지 않게 한번에 작업 할 수 있게 해준다.
 
 
오라클 9i 버전부터 가능하며, 오라클 10g 버전부터는 DELETE 구문도 가능.
 
 
1).대상 테이블에서 조건에 따라 행을 삽입 또는 업데이트합니다.
    대상 테이블에 행이 존재하는 경우 하나 이상의 열을 업데이트하고, 그렇지 않으면 새 행에 데이터를 삽입합니다.
 
2).두 테이블을 동기화합니다.
    원본 데이터와의 차이점에 따라 대상 테이블에서 행을 삽입, 업데이트 또는 삭제합니다.
 
 
MERGE 의 신텍스.
 
MERGE INTO 테이블명  별칭
    USING 대상테이블/뷰  별칭
    ON 조인조건
    WHEN MATCHED THEN
    UPDATE SET
    컬럼1=값1
    컬럼2=값2
  WHEN NOT MATCHED THEN
      INSERT (컬럼1,컬럼2,...)
      VALUES(값1,값2,...);
 
 
◇ FORMAT
─────────────────────────────────────────────────────────────────────────────
MERGE INTO desc_table_name [alias]
    USING (source_table_name or view or subquery) [alias]
    ON (join condition)
    WHEN MATCHED THEN
    UPDATE SET col1 = value1[, col2 = value2…]
    WHEN NOT MATCHED THEN
    INSERT [(col1, col2, ... coln)]
    VALUES(value1, value2 ... valuen)
 
 
desc_table_name ........... UPSERT¹ 하고자 하는 테이블명
 
alias ..................... 조인조건, UPDATE, INSERT등레 사용될 Alias명
 
source_table_name ......... UPSERT할 값이 들어있는 테이블명
 
view ...................... UPSERT할 값이 들어있는 뷰테이블명
 
subquery .................. UPSERT할 값을 SELECT 한 쿼리문장
 
join condition ............ UPSERT하기위한 조건 (WHERE절에 해당한다)
 
지정한 join condition에 의하여 그 값이 일치하면 UPDATE를 일치하지 않으면 INSERT를 수행한다.
 
※ ON 절에 기술된 컬럼이 WHEN MATCHED THEN 다음에 오는 UPDATE 문장에는 올수없다
 
즉, KEY에 해당하는 부분일 수 있으므로 조건에 해당된 컬럼이 UPDATE되는 것을 방지한다.
 
─────────────────────────────────────────────────────────────────────────────
 
;;;;
 
 
/**************************/
 
1).대상 테이블에서 조건에 따라 행을 삽입 또는 업데이트합니다.
    대상 테이블에 행이 존재하는 경우 하나 이상의 열을 업데이트하고, 그렇지 않으면 새 행에 데이터를 삽입합니다.
  ;;
 
 
 
/*에러.
ORA-38104: Columns referenced in the ON Clause cannot be updated

문제 상황
MERGE INTO를 사용하여 UPDATE 시 위와 같은 에러가 발생하였다.

문제 원인
MERGE INTO (~~) USING (~~) ON (~~) 구문 사용 시, ON에서 사용한 컬럼을 UPDATE 할 수 없다.

문제 해결
에러가 발생하는 조건문은 ON에서 사용하지 말고 UPDATE의 WHERE 문에서 사용한다.
*/
;;
 
MERGE INTO DEPTT
      USING DUAL ON (DEPTNO = '10')
    WHEN MATCHED THEN
      UPDATE
      SET  dname = '인사부', loc = '서울'
    WHEN NOT MATCHED THEN
      INSERT
      (DEPTNO, dname, loc)
      VALUES
      ('10', '인사부', '서울');
 
 
MERGE INTO DEPTT
      USING DUAL ON (DEPTNO = '10')
    WHEN MATCHED THEN
      UPDATE
      SET dname = '인사부', loc = '서울'
    WHEN NOT MATCHED THEN
      INSERT
      (DEPTNO, dname, loc)
      VALUES
      ('10', '인사부', '서울');
 
 
SELECT * FROM DEPTT;
 
 
      설명
MERGE INTO DEPTT                              -- DEPTT 테이블에 넣어요.
      USING  DUAL                             -- dual을 쓰면 1개의 테이블만 쓸 때. 대상 테이블이 필요 없을 때.
      ON (DEPTNO = '10' AND dname = '인사부')                      -- deptno가 10번인.(비교조건)
    WHEN MATCHED THEN                         -- 같다면,
      UPDATE                                  -- 업데이트 해요
      SET dname = '인사부', loc = '부산'            -- DEPTT 테이블의 dname과 loc를 값 입력.
    WHEN NOT MATCHED THEN                 -- 같지 않다면,
      INSERT                              -- 입력해요
          (deptno, dname, loc)            -- DEPTT 테이블의 deptno, dname, loc 필드에
      VALUES                              -- 값들을
          ('10', '인사부', '서울');                     -- 테이블의 deptno, dname, loc 필드 값들을요.
 
SELECT * FROM DEPTT;
 
 
2).두 테이블을 동기화합니다.
    원본 데이터와의 차이점에 따라 대상 테이블에서 행을 삽입, 업데이트 또는 삭제합니다.
 
 
SELECT * FROM deptt;
SELECT * FROM depttt;
 
 
MERGE INTO DEPTT t
      USING  DEPTTT d
      ON (d.dept_no = t.deptno)
    WHEN MATCHED THEN
      DELETE
      FROM DEPTT
    WHEN NOT MATCHED THEN
      INSERT
          (deptno, dname, loc)
      VALUES
          (d.dept_no, d.dept_name, d.location);
 
 
      설명
MERGE INTO DEPTT t                                                 -- DEPTT 테이블에 넣어요.
      USING  DEPTTT d                                      -- DEPTTT (d) 테이블을 이용해요.
      ON (d.dept_no = t.deptno)                            -- t.deptno 와 d.deptno가 같은 것을요.
    WHEN MATCHED THEN                                      -- 같다면,
      UPDATE                                               -- 업데이트 해요
      SET t.dname = d.dept_name, t.loc = d.location      -- d테이블의 dname과 loc를 t테이블의 dname와 loc로
    WHEN NOT MATCHED THEN                                 -- 같지 않다면,
      INSERT                                            -- 입력해요
          (deptno, dname, loc)                        -- t테이블의 deptno, dname, loc 필드에
      VALUES                                            -- 값들을
          (d.dept_no, d.dept_name, d.location);     -- d테이블의 deptno, dname, loc 필드 값들을요.
 
 
 
SELECT * FROM deptt;
 
SELECT * FROM TDEPT;
 
 
 
 
 
SELECT t.location as loc
FROM depttt t,
    (SELECT DISTINCT(area) AS loc
      FROM TDEPT
     WHERE area = '서울'
    )td
WHERE t.location = td.loc
 
 
 
 
 
MERGE INTO DEPTT t                                     -- dept_temp (t) 테이블에 넣어요.
      USING
    (SELECT t.dept_no, t.DEPT_NAME, t.location
      FROM depttt t
         ,
           (SELECT DISTINCT(area) AS loc
             FROM TDEPT
            WHERE area = '서울'
           )td
     WHERE t.locationd= td.loc
    )
                          -- dept         (d) 테이블을 이용해요.
      ON (d.dept_no = t.deptno)                -- t.deptno 와 d.deptno가 같은 것을요.
    WHEN MATCHED THEN                         -- 같다면,
      UPDATE                                   -- 업데이트 해요
      SET t.dname = d.dept_name, t.loc = d.location      -- d테이블의 dname과 loc를 t테이블의 dname와 loc로
    WHEN NOT MATCHED THEN                     -- 같지 않다면,
      INSERT                                    -- 입력해요
          (deptno, dname, loc)                    -- t테이블의 deptno, dname, loc 필드에
      VALUES                                    -- 값들을
          (d.dept_no, d.dept_name, d.location);             -- d테이블의 deptno, dname, loc 필드 값들을요.
 
 
SELECT * FROM deptt;
 
 
 
 
마무리.
 
 
 
오라클에서 MERGE INTO 구문은자주 사용하는 쿼리 구문이니 익혀두고 이용하면 좋다.
 
단, 9i이상의 버전에서만 가능하다고 했지만 9i의 MERGE 구문은 불완전 (DELETE 구문도 10g 부터 사용가능)하고
구문상 제약사항이 많기 때문에 ON 구문에서 조건식을 잘못 넣으면 작동이 안된다.
10g 이상부터 구문을 자유롭게 이용할 수 있다.
또한 TRIGGER 발생이 안된다는 것도 주의 해야 한다.
당연한 얘기겠지만 단순 INSERT 구문이나 UPDATE 구문을써도 문제가 안되는데 굳이 MERGE를 남발하는 것도 성능이 떨어지기 때문에
꼭 필요할 때 잘 쓰면 좋다. (100~200만건 이상 TABLE에서는 느려진다고 한다.)