Oracle SQL 강좌
MERGE 문의 이해 및 활용 5 23 99,999+

by 구루비 MERGE [2006.02.12]


MERGE 문의 이해

  • - MERGE문은 조건에 따라서 데이터의 삽입,갱신,삭제 작업을 한번에 할 수 있다.
  • - 해당 행이 존재하는 경우 UPDATE(DELETE포함 할 수 있음)를 수행하고, 새로운 행일경우 INSERT를 수행한다.
  • - 대상 테이블에 대한 UPDATE/INSERT 조건은 ON절에 의해 결정된다.
  • - MERGE문에서 CLOB 사용시 업데이트 할 내용이 2000bytes가 넘을때 ORA-00600 오류가 발생하며, patch set 11.2.0.2 버전으로 해결할 수 있다.

MERGE Syntax
MERGE [ hint ]
   INTO [ schema. ] { table | view } [ t_alias ]
   USING { [ schema. ] { table | view }
         | subquery 
         } [ t_alias ]
   ON ( condition )
WHEN MATCHED THEN
UPDATE SET column = { expr | DEFAULT }
           [, column = { expr | DEFAULT } ]...
[ DELETE where_clause ]
WHEN NOT MATCHED THEN
INSERT [ (column [, column ]...) ]
VALUES ({ expr [, expr ]... | DEFAULT })
  • - INTO : DATA가 UPDATE되거나 INSERT 될 테이블 또는 뷰를 지정.
  • - USING : 비교할 SOURCE 테이블 또는 뷰나 서브쿼리를 지정, INTO절의 테이블과 동일하거나 다를 수 있다.
  • - ON : UPDATE나 INSERT를 하게 될 조건으로, 해당 조건을 만족하는 DATA가 있으면 WHEN MATCHED 절을 실행하게 되고, 없으면 WHEN NOT MATCHED 이하를 실행하게 된다.
  • - WHEN MATCHED : ON 조건절이 TRUE인 ROW에 수행 할 내용 (UPDATE, DELETE포함 될 수 있음)
  • - WHEN NOT MATCHED : ON 조건절에 맞는 ROW가 없을 때 수행할 내용 (INSERT)

MERGE 예제1

아래 예제는 부서번호 20,30의 사원이 존재하면 급여를 10% 인상하고, 존재하지 않으면 급여가 1000보다 큰 사원정보를 등록하는 예이다. (INSERT, UPDATE 예제)


-- 부서번호 10, 20의 사원정보를 가지는 테스트 테이블을 생성하자 
CREATE TABLE emp_merge_test 
AS SELECT empno, deptno, sal FROM emp WHERE deptno IN (10, 20);


-- 데이터를 확인해 보자
SELECT * FROM emp_merge_test; 


-- 사원이 존재하면 급여를 10% 인상하고, 없으면 INSERT 한다.
MERGE INTO emp_merge_test m
USING ( SELECT empno, deptno, sal    -- USING절에 뷰가 올수 있다.
        FROM emp 
        WHERE deptno IN (20,30)) e
ON ( m.empno = e.empno)
WHEN MATCHED THEN 
  UPDATE SET m.sal = ROUND(m.sal*1.1)
WHEN NOT MATCHED THEN 
INSERT (m.empno, m.deptno, m.sal)
VALUES (e.empno, e.deptno, e.sal)
WHERE e.sal > 1000                 -- INSERT 절의 조건절도 지정이 가능하다
COMMIT;


-- 20부서의 급여가 10%증가했고, 30부서는 등록되었는지 확인해 보자
SELECT * FROM emp_merge_test;


-- 다음 테스트를 위해서 emp_merge_test 테이블에 30부서 데이터를 삭제하자
-- 부서번호 10과 20의 사원정보만 남을 것이다. 
DELETE FROM emp_merge_test WHERE deptno = 30;
COMMIT;

MERGE 예제2

아래는 부서번호 10의 사원 급여를 10% 인상하고, 부서번호 20의 사원정보는 삭제하며, 부서번호 30의 사원 급여를 20% 인상하는 예이다. (INSERT, UPDATE, DELETE 예제)

-- 30 부서가 삭제되었는지 확인한다.
SELECT * FROM emp_merge_test;


-- MERGE 문 작성
MERGE INTO emp_merge_test m
USING emp e
ON (m.empno = e.empno)
WHEN MATCHED THEN 
  UPDATE SET m.sal = ROUND(m.sal*1.1)
  DELETE WHERE (m.deptno = 20)   -- 부서번호 20의 사원정보는 삭제.
WHEN NOT MATCHED THEN 
INSERT (m.empno, m.deptno, m.sal)
VALUES (e.empno, e.deptno, ROUND(e.sal*1.2));
COMMIT;


-- 정상적으로 변경되었는지 확인해 보자
SELECT * FROM emp_merge_test;


-- 테스트 테이블은 삭제하자
DROP TABLE emp_merge_test;

참고링크

- 강좌 URL : http://www.gurubee.net/lecture/2225

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 오동규 [2006.03.10 00:53:03]
참고로 merge 문에서 10g 에서는 delete 문이 추가 되었습니다.^^

by 작살 [2006.06.08 10:06:44]
한가지...의문스러운 것이 있어서요..
merge함수는 join 조건이 있는경우, 즉 table이 두개 이상인 경우에만 사용가능한가요? table이 하나인 경우 test해봤는데 안되는거 같아서요..;
그동안 로직을 구현해서 사용하다가 이함수를 보고 이거다 싶었는데...
관리자님..알려주십시오....^^;;

by 이지웅 [2006.07.06 10:15:28]
너무공부를 안했어요..이제 와서야 merge함수를 알게되다니..근데 궁금한점!
merge함수 insert 절에서 레코드를 모두 써야되나요?
insert emp(dept_no) values(:dept_no)이런 방법으로 입력컬럼을 정의하여 사용하려면 어떻게 작성해야 될까요??

by 삼식이 [2006.08.20 03:41:58]
MERGE 예제문에서 e.empno 대신에 그냥 값을 넣을순 없을까요??

by 용쟁호투 [2006.09.28 16:39:31]
커서를 Merge해주는 함수는 없나요?

by kaiger [2006.12.11 10:43:35]
하나도 모르게따 ㅠㅠ

by 남경일 [2009.08.14 11:29:51]
MERGE INTO사용시 주의점이 있네요..
1. Oracle 에서만 사용 가능함. ( 9i 이상에서만.. )
2. Trigger 발생 안함.
3. 조건절에 사용한 컬럼은 UPDATE 불가. (조건절은 KEY만 사용하는게 좋음. )

ORA-00904:열명이 부적합합니다. 라는 오류가 발생하여 확인결과
3번에 걸려 저도 원인을 찾느라 고생했네요 ^^

출처 : http://cafe.naver.com/hermeswing.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=493

by 타락천사 [2009.08.26 13:59:54]
위의 제약사항 중
2. Trigger 발생 안함.
타켓 테이블에 대한 Trigger를 이야기 하시는거면 잘못된 것으로 보이네요

by 타락천사 [2009.08.26 14:01:02]
Online 메뉴얼
If the update clause is executed, then all update triggers defined on the target table are activated.
If the insert clause is executed, then all insert triggers defined on the target table are activated.

by 김컴 [2011.08.04 16:04:04]
블로그로 퍼갑니다..
근데 merge에서요 insert 할때 PK값이 있는데 그 값을 화면에서 받아서(다른 테이블의값) 넣어줄수는 없나요? 컬럼안쓰고 바로 값으로요..


항상 감사합니다!!
10g에서는 delete문도 추가되었다니 우와!! 완젼 편하네요..

by 아발란체 [2012.12.07 09:06:49]

작살 / 상위 버전 오라클에서 돌려보세요. 또는 하위 버전에서도 힌트 부분을 가상 테이블과 관계를 두면 처리가 됩니다. 하위 버전에서는 버그라기 보다 관계 중심으로 명확하게 짜지 않으면 에러는 없는데 분기가 아무데도 안되는 경우가 있습니다.


by 이리나인 [2012.12.17 18:25:30]

아직까진 이해도가 조금 딸리네요..ㅠㅠ
잘보고 갑니다~

by 초록짱 [2013.04.16 10:34:37]
잘 보고 갑니다.

by pig [2014.08.06 09:21:02]

로드맵 정주행중입니다.

좋은게시물 감사합니다.


by 주정래 [2014.11.13 15:38:57]

MERGE 예제2 의 11 번라인

UPDATE SET m.sal = ROUND(m.sal*1.1)

DELETE WHERE (m.deptno = 20)   -- 부서번호 20의 사원정보는 삭제

 

update 와 delete 두가지가 동시에 안되는데요..  9i 입니다.

 


by 귀여니^^ [2014.12.23 12:51:21]

10g 부터 delete 문이 추가 된듯 합니다..^^


by 디비의신 [2015.02.02 17:58:35]

머지하게 되네요 머지문 ㅋㅋ 잘 배워갑니다 첨보는거라 오잉하지만..


by 종맨 [2016.02.18 14:59:37]

저는 예제1번에서 부터 ora-30926 에러가 나네요..

on절에 쿼리가 잘못된건가요??;;;


by 종맨 [2016.02.18 15:06:17]

앗, 해결했습니다 제 emp테이블에 문제가 있었네요...ㅠㅠ


by 껍질파괴 [2016.07.19 22:07:34]
MERGE INTO emp_merge_test m
USING emp e
ON (m.empno = e.empno)
WHEN MATCHED THEN
  UPDATE SET m.sal = ROUND(m.sal*1.1) where m.dept = 10
  DELETE WHERE (m.deptno = 20)   -- 부서번호 20의 사원정보는 삭제.
WHEN NOT MATCHED THEN
INSERT (m.empno, m.deptno, m.sal)
VALUES (e.empno, e.deptno, ROUND(e.sal*1.2));
COMMIT;
 
위의 예제를 테스트해보다 When matched then 구절에 
예제와 다르게 where m.dept = 10이라는 문구를 추가하니 delete가 작동하지 않았습니다. 
이거는 어떠한 이유로 인해서 delete문이 실행되지 않은 것인지 궁금합니다. 
사용하는 버전은 오라클 11gr2입니다. 

by 마농 [2016.07.20 09:39:21]

MERGE 에서의 DELETE 구문은
DELETE 단독 구문이 아닌 UPDATE 구문에 종속됩니다.
UPDATE 실행된 건에 한해서 DELETE 구문 동작합니다.


따라서 10번부서는 Update 20번부서는 Delete 하려면
Update 구문의 Where 절을 다음과 같이 바꿔주시면 됩니다.
WHERE m.deptno IN (10, 20)


by 고인물 [2018.06.15 17:47:35]

안녕하세요 초보자입니다..

예제2번이요 

ㄱ - 아래는 부서번호 10의 사원 급여를 10% 인상하고
ㄴ - 부서번호 20의 사원정보는 삭제하며
ㄷ - 부서번호 30의 사원 급여를 20% 인상하는 예이다.

ㄱㄴㄷ 중에 ㄴ 만 이해가되네요.. 도와주세요..

* WHEN MATCHED : ON 조건절이 TRUE 인 ROW에 수행 할 내용 (UPDATE, DELETE 포함될수있음)

* WHEN NOT MATCHED : ON 조건절에 맞는 ROW가 없을 때 수행할 내용 (INSERT)

ㄱ > 부서번호 10의 사원급여를 10% 인상인데 10이라는 조건이 어디있나요 ?

ㄷ > 부서번호 30의 사원급여를 20% 인상인데 부서번호 30이라는조건이 어디에있는지..

ON 절에 m.empno=e.empno 만으로는 잘이해가안되네요 .. 도와주세요 

 

예제2)

MERGE INTO emp_merge_test m

USING emp e

ON (m.empno = e.empno)

WHEN MATCHED THEN

  UPDATE SET m.sal = ROUND(m.sal*1.1)

  DELETE WHERE (m.deptno = 20)   -- 부서번호 20의 사원정보는 삭제. 

WHEN NOT MATCHED THEN

INSERT (m.empno, m.deptno, m.sal)

VALUES (e.empno, e.deptno, ROUND(e.sal*1.2));

 


by 엘시아 [2019.04.22 09:56:16]

안녕하세요 1년가까이 시간이 지났는데, 제가 이해한것을 바탕으로 적어볼께요 .(이후에 볼사람들을 위해 ㅎㅎ)

ㄱ - 아래는 부서번호 10의 사원 급여를 10% 인상하고
ㄴ - 부서번호 20의 사원정보는 삭제하며
ㄷ - 부서번호 30의 사원 급여를 20% 인상하는 예이다.

-------------------------------------------------------------------------------------

ㄱ > 부서번호 10의 사원급여를 10% 인상인데 10이라는 조건이 어디있나요 ?

    -> UPDATE SET m.sal = ROUND(m.sal*1.1)  

        1.1을 곱하는 것 자체가 10%인상입니다. 만약 m.sal이 100이면, 1.1을 곱하면 110이되니 10% 인상)

       마농님이 위에  답변을 보시면 update문 실행된 로우안에서 delete가 탄다고했으니 20부서를 지우니 10부서만 남았네요 부서 10번만 남았으니 조건에 만족합니다. 삭제전에 10번 20번부서를 모두 10%인상후 반올림했는데, 20부서를 지웠습니다.

ㄷ > 부서번호 30의 사원급여를 20% 인상인데 부서번호 30이라는조건이 어디에있는지..

ON 절에 m.empno=e.empno 만으로는 잘이해가안되네요 .. 도와주세요 

 ->  emp테이블에 부서가 어디까지 있는지를 모르기때문에  정확하게 정의했으면 문제가 없었을 것 같습니다. (emp 테이블에 부서가 10~30까지밖에없다고하면 예제2 번 merge문은 문제가 없습니다.)

확실하게 하려면 insert 문에 예제 1번처럼 where 조건을 넣어야 합니다.  ( where e.deptno ='30' )

 

예제2)

MERGE INTO emp_merge_test m

USING emp e

ON (m.empno = e.empno)

WHEN MATCHED THEN

  UPDATE SET m.sal = ROUND(m.sal*1.1) -- 1.1 곱이 10% 인상

  DELETE WHERE (m.deptno = 20)   -- 부서번호 20의 사원정보는 삭제.   -- 테이블에 부서가 10번,20번중에 20번을 지우니 부서번호 10번만 남음

WHEN NOT MATCHED THEN   -- emp 테이블에 부서가 30까지라고하면 맞지만 40,50같은 부서가 존재하면 잘못된 merge문이 되버림, 확실하게 하려면 insert 문에 예제 1번처럼 where 조건을 넣으면 됨.  ( where e.deptno ='30' )

INSERT (m.empno, m.deptno, m.sal)

VALUES (e.empno, e.deptno, ROUND(e.sal*1.2));

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입