MERGE 문 사용법 (DUAL, UPDATE와 INSERT를 한번에)

MERGE 문을 사용하면 변경할 테이블에 데이터가 존재하는지 체크하고, UPDATE, DELETE, INSERT를 한 번에 작업이 가능하다. MERGE 문을 사용하지 않을 경우 해당 조건으로 테이블을 SELECT 한 후 IF 조건을 사용하여 UPDATE나 INSERT로 분기하는 로직을 작성해야 하는 번거로움이 있다.

 

MERGE 문의 경우 단일(한개의) 테이블에 UPDATE 또는 INSERT를 하는 경우 많이 사용하지만, 두개의 테이블을 비교하거나 서브 쿼리의 결과에 따라서 UPDATE, INSERT 작업이 가능하다.

 

MSSQL MERGE 문

단일 테이블 사용법 (DUAL)

오라클에서는 DUAL이라는 dummy 테이블을 USING 절에 사용하면 단일 테이블 작업이 간단하지만, MSSQL에서는 DUAL 테이블이 없기 때문에 dummy 서브 쿼리를 사용하면 된다.

 

MERGE INTO dept AS a
USING (SELECT 1 AS dual) AS b
   ON (a.deptno = 50)
 WHEN MATCHED THEN
   UPDATE SET a.dname = 'IT', a.loc = 'SOUTHLAKE'
 WHEN NOT MATCHED THEN
   INSERT(deptno, dname, loc) VALUES(50, 'IT', 'SOUTHLAKE')
;

 

(SELECT 1 AS dual) AS b 이부분은 dummy 서브 쿼리 이므로 그대로 복사해서 사용하면 된다.

dept 테이블에 deptno = '50'에 만족하는 값이 있으면 UPDATE, 없으면 INSERT 한다.

 

DECLARE @deptno INT = 50
DECLARE @dname NVARCHAR(14) = 'IT'
DECLARE @loc NVARCHAR(13) = 'SOUTHLAKE'

MERGE INTO dept AS a
USING (SELECT 1 AS dual) AS b
   ON (a.deptno = @deptno)
 WHEN MATCHED THEN
   UPDATE SET a.dname = @dname, a.loc = @loc
 WHEN NOT MATCHED THEN
   INSERT(deptno, dname, loc) VALUES(@deptno, @dname, @loc)
;

 

동일한 쿼리문을 조금 더 이해하기 쉽도록 변수를 사용하여 작성하다.

 

서브 쿼리를 이용하는 방법

DECLARE @deptno INT = 50

MERGE INTO dept AS a
USING (SELECT DISTINCT 
              d.deptno AS deptno
            , d.dname  AS dname
            , d.loc    AS loc
         FROM emp AS e
        INNER JOIN dept_history AS d
           ON e.deptno = d.deptno
        WHERE e.deptno = @deptno) AS b
   ON (a.deptno = b.deptno)
 WHEN MATCHED THEN
   UPDATE SET a.dname = b.dname
            , a.loc   = b.dname
 WHEN NOT MATCHED THEN
   INSERT(deptno, dname, loc) 
   VALUES(b.deptno, b.dname, b.loc)
;

 

USING 절에 서브 쿼리를 사용하는 방법을 설명한 쿼리이다.

emp 테이블에 deptno = '50'이 존재하고, 서브 쿼리 결과와 dept 테이블을 비교하여 존재 여부에 따라서 UPDATE, INSERT 한다.

 

두개의 테이블 조인하는 방법

MERGE INTO dept AS a
USING dept_history AS b
   ON (a.deptno = b.deptno)
 WHEN MATCHED THEN
   UPDATE SET a.dname = b.dname
            , a.loc   = b.loc
 WHEN NOT MATCHED THEN
   INSERT(deptno, dname, loc) 
   VALUES(b.deptno, b.dname, b.loc)
;

 

dept_history 테이블의 값이 dept 테이블에 존재하는 경우, dept_history 테이블의 값으로 UPDATE, 없으면 INSERT 한다.

 

기타 사용법

ON 절에 WHERE 절과 유사하게 AND, OR 를 사용하여 여러개의 조건을 부여할 수 있다.

WHEN 절에도 MATCHED, NOT MATCHED 외에 추가로 조건을 부여할 수 있다.

 

MERGE INTO dept AS a
USING dept_history AS b
   ON (a.deptno = b.deptno)
 WHEN MATCHED THEN
   UPDATE SET a.dname = b.dname, a.loc = b.loc
 WHEN NOT MATCHED BY TARGET THEN
   INSERT(deptno, dname, loc) VALUES(b.deptno, b.dname, b.loc)
 WHEN NOT MATCHED BY SOURCE THEN
   DELETE
;

 

NOT MATCHED 인 경우 BY TARGET와 BY SOURCE 를 사용할 수 있다. 

 

NOT MATCHED BY TARGET (= NOT MATCHED)

TARGET 테이블에 데이터가 없는 경우 TARGET 테이블에 INSERT

NOT MATCHED와 동일 하므로 BY TARGET는 생략해도 된다

 

NOT MATCHED BY SOURCE

SOURCE 테이블에는 없고 TARGET 테이블에만 존재하는 데이터를 TARGET 테이블에서 DELETE

NOT MATCHED BY SOURCE

DELETE 문에는 WHERE 조건문을 작성하지 않는다.

필요시 WHEN 절에 조건을 작성한다.

 

MERGE 문 사용 시 주의사항

USING 절에 별칭이 없을 경우

오류 메시지 : 키워드 'ON' 근처의 구문이 잘못되었습니다.

 

쿼리문 끝에 세미콜론이 없을 경우

오류 메시지 : MERGE 문은 세미콜론(;)으로 종료해야 합니다.

 

USING 절의 데이터에 변경할 테이블과 비교할 테이블의 KEY 컬럼 값이 중복으로 존재 할 경우

오류 메시지 : MERGE 문이 동일한 행을 여러 번 UPDATE 또는 DELETE하려고 했습니다. 대상 행이 둘 이상의 원본 행과 일치하면 이런 경우가 발생합니다. MERGE 문은 대상 테이블의 동일한 행을 여러 번 UPDATE/DELETE할 수 없습니다. ON 절을 구체화하여 대상 행이 하나의 원본 행하고만 일치하도록 하거나 GROUP BY 절을 사용하여 원본 행을 그룹화하십시오.