[ Oracle ] MERGE INTO 문 사용법 및 예제

---

드디어 진행하고 있는 프로젝트 개발이 막바지에 왔습니다.

들어오는 요구 사항이 물 밀듯이 밀려오다 보니 야근을 많이 하다 보니 블로그 포스팅을 못했는데

프로젝트하면서 나왔던 오류라든지 혹은 배워야 하는 부분에 대해 하나씩 정리해보려고 합니다.

 

오늘은 Query 가공하면서 가장 고생했던 MERGE INTO 문에 대해 알아보려고 합니다.

 

이전 프로젝트에서도 MERGE문을 사용할 경우가 종종 있었는데

주로 데이터 마이그레이션이나 혹은 데이터 INSERT 하는 경우 사용합니다.

 

특히 기존 테이블에 데이터를 INSERT 하면 기존 데이터와 겹치지 않게 하기 위해 MERGE INTO 문을 사용합니다.

 

이 부분에서 그러면 데이터 INSERT 하는데 굳이 MERGE INTO 문을 사용해야 할까?

의문에 빠질 수 있지만, 데이터 INSERT 할 때 확신이 없다면 MERGE INTO 문을 사용하는 것을 추천드립니다.

 

실무에서는 안전하게 작동할 수 있는 쿼리를 작성해야 하기 때문에

저도 이번 프로젝트에서 MERGE INTO 문 위주로 쿼리를 작성했습니다. 

 

그럼 바로 개념 및 사용법에 대해 알아봅시다.

---

MERGE INTO 문

 

MERGE 문은 데이터를 조작하고 조건에 따라 테이블을 UPDATE 하거나

INSERT, DELETE 하는 데 사용되는 SQL문입니다.

 

조건이 맞으면 UPDATE, DELETE를 수행하고 그렇지 않으면 INSERT를 수행합니다.

위에서 언급했듯 MERGE 문은 데이터 마이그레이션을 진행하면서 데이터를 추가할 때 사용합니다.

 

주로 두 개 이상의 테이블 간의 데이터를 조작하거나 동기화할 때 유용한데

이것은 일반적으로 "병합" 또는 "병합 조작"으로 알려져 있습니다.

 

예제를 통해서 알아봅시다.

 

MERGE INTO team t
USING member m
ON (t.member_id = m.member_id)
WHEN MATCHED THEN
    UPDATE SET t.name = m.name, t.email = m.email
WHEN NOT MATCHED THEN
    INSERT (team_id, name, email)
    VALUES (m.member_id, m.name, m.email);

 

위 에제 SQL문을 보면 

TEAM 테이블이 대상 테이블로 사용되고,

MEMBER 테이블이 소스 테이블로 사용됩니다.

 

MEMBER 테이블의 레코드가 TEAM 테이블과 일치하는 경우에는

TEAM 테이블의 해당 레코드를 UPDATE 하고, 일치하는 레코드가 없는 경우에는 TEAM 테이블에 MEMBER 테이블의 해당 레코드를 삽입합니다.

 

여기서 주의할 점은

ON (t.member_id = m.member_id)

 

ON 절을 확인해보면

대상 테이블과 소스 테이블 간의 일치 여부를 확인하기 위한 조인 조건을 지정하는 부분입니다

 

만약 ON 절에 있는 조건과 UPDATE 대상 컬럼 값이 같이 들어가 있으면 "ORA-38104" 오류가 발생하는데 해당 에러는 ON 절에서 참조되는 열을 갱신할 수 없다고 알려줍니다.

 

위 예제로 사용된 방법은 조인을 사용하는 방법으로 진행했는데

조인뿐만 아니라 단일 테이블 사용법, 서브쿼리, WHERE, DELETE 절 사용 방법에 대해 바로 알아보겠습니다.

---

DUAL(단일 테이블 사용)

 

단일 테이블을 사용하는 MERGE 문 코드를 확인 먼저 하겠습니다.

MERGE INTO team t
USING (
    SELECT 'kim' AS name, 'kim@naver.com' AS email FROM DUAL
) m
ON (t.member_id = 1)
WHEN MATCHED THEN
    UPDATE SET t.name = m.name, t.email = m.email
WHEN NOT MATCHED THEN
    INSERT (team_id, name, email)
    VALUES (1, m.name, m.email);

 

위 예제를 확인해 보면 소스 데이터가 실제 테이블이 아니라 DUAL 테이블을 사용했습니다.

 

MERGE 문에서 DUAL을 사용하는 이유는 크게 세 가지 장점이 있습니다.

 

1. 소스 데이터의 일괄 처리

 

일반적으로 단일 테이블 사용 방법에서는 실제 데이터가 아닌 상수 값을 사용하여 MERGE 문을 실행하는 경우가 많습니다.

 

이 경우, DUAL 테이블을 사용하여 상수 값을 생성하고 해당 값을 MERGE 문에 전달할 수 있습니다.

---

2. 구문상의 요구 사항

 

Oracle에서는 MERGE 문의 구문상에서 소스 데이터를 지정하는 데에는 실제 테이블을 사용해야 합니다.

 

그러나 단일 테이블을 사용하는 경우 소스 데이터가 실제 테이블이 아니라면,

구문적인 문제가 발생합니다.

 

이를 회피하기 위해 DUAL과 같은 가상의 테이블을 사용합니다.

---

3. 성능 향상

 

DUAL 테이블은 실제 데이터가 없는 가상의 테이블입니다.

데이터베이스 관리 시스템이 처리할 필요가 없어서 성능상 이점이 있을 수 있습니다.

---

따라서 위 예제 코드에서 소스 데이터가 실제 테이블이 아니라 DUAL 테이블을 사용했습니다.

SELECT 문에서 "kim"과 "kim@naver.com"이라는 상수 값을 DUAL 테이블에서 가져와 MERGE 문에 전달합니다.

 

그리고 ON 조건절을 통해 일치하면 UPDATE, 불일치하면 INSERT를 진행합니다.

---

JOIN 사용 방법

 

JOIN을 사용하여 MERGE 문을 작성하는 경우에는 조인 조건을 MERGE 문의 ON 절에 정의해야 합니다.

 

주로 여러 테이블 간의 복잡한 조작에 조인 사용 방법이 유용합니다.

MERGE INTO members m
USING (SELECT member_id, name, email FROM members_new) mn
ON (m.member_id = mn.member_id)
WHEN MATCHED THEN
    UPDATE SET m.name = mn.name, m.email = mn.email
WHEN NOT MATCHED THEN
    INSERT (member_id, name, email)
    VALUES (mn.member_id, mn.name, mn.email);

---

서브쿼리 사용 방법

 

서브쿼리는 주로 소스 데이터가 다른 쿼리의 결과인 경우에 사용되는데

소스 데이터를 가져와서 MERGE 문에 사용할 수 있습니다.

 

MERGE INTO members m
USING (
    SELECT member_id, name, email FROM (
        -- SELECT 조회 !!
        SELECT * FROM members_new
    )
) mn
ON (m.member_id = mn.member_id)
WHEN MATCHED THEN
    UPDATE SET m.name = mn.name, m.email = mn.email
WHEN NOT MATCHED THEN
    INSERT (member_id, name, email)
    VALUES (mn.member_id, mn.name, mn.email);

 

---

WHERE 절 사용 방법

 

WHERE 절은 JOIN 조건 이외의 추가 조건을 지정하는 데 사용됩니다.

 

예를 들어, 특정 조건을 충족하는 소스 레코드만 병합하고 싶은 경우 WHERE 절을 사용할 수 있습니다.

MERGE INTO members m
USING members_new mn
ON (m.member_id = mn.member_id)
WHEN MATCHED AND mn.active_flag = 'Y' THEN
    UPDATE SET m.name = mn.name, m.email = mn.email
WHEN NOT MATCHED THEN
    INSERT (member_id, name, email)
    VALUES (mn.member_id, mn.name, mn.email)
    WHERE mn.active_flag = 'Y';

 

WHERE 절을 사용할 때는

JOIN 조건 이외에 추가 조건을 제공하는 것이 중요합니다. 

 

그러나 이 추가 조건은 소스 테이블의 조건을 지정하는 것이 아니라 대상 테이블의 조건을 지정해야 합니다.

---

DELETE 절 사용 방법

 

DELETE 절은 MERGE 문에서 일치하는 대상 레코드를 삭제하는 데 사용됩니다.

 

일반적으로 DELETE 절은 WHEN MATCHED 절 내에서 사용하는데,

실무에서는 HISTORY 테이블 등 실제로 삭제하는 게 아닌, UPDATE를 하는 경우가 많기 때문에 

자주 사용하지는 않을 거 같습니다.

 

글쓴이도 이번 프로젝트에서 DELETE 절을 사용하지 않았습니다.

 

MERGE INTO members m
USING members_new mn
ON (m.member_id = mn.member_id)
WHEN MATCHED THEN
    UPDATE SET m.name = mn.name, m.email = mn.email
    DELETE WHERE mn.marked_for_deletion = 'Y'
WHEN NOT MATCHED THEN
    INSERT (member_id, name, email)
    VALUES (mn.member_id, mn.name, mn.email);

 

추가로 DELETE 절을 사용할 때는 

대상 테이블에서만 레코드를 삭제할 수 있으며, 소스 테이블의 레코드를 삭제할 수 없습니다.

 

MERGE 문을 사용할 때에는 데이터 무결성을 유지하기 위해 주의해야 합니다.

JOIN 조건 및 일치, 불일치 조작에 대해 신중하게 생각해야 하므로,

자주 사용해 보시는 걸 추천드립니다.

---

마치며

 

오늘은 MERGE INTO 문을 예제를 통해 사용법에 대해 알아봤습니다.

다음 포스팅에서 뵙겠습니다.