MERGE 구문은 ON 절에 WHEN MATCHED THEN, WHEN NOT MATCHED THEN 절을 이용하여 총 3 가지 유형의 트랜잭션 처리를 할 수 있다.

CASE1)UPDATE & DELETE & INSERT 또는 UPDATE & INSERT (순서 상관 없음)

MERGE INTO MERGE_T1 tt
USING (
SELECT  c1, c2, c3
FROM MERGE_T2
WHERE c1 >= 99990
AND c1 <= 100090
) st
ON ( tt.c1 = st.c1 )
WHEN MATCHED THEN
UPDATE SET tt.c2 = st.c2, tt.c3 = st.c3
DELETE WHERE (tt.c2 = 'A')
WHEN NOT MATCHED THEN
INSERT (tt.c1, tt.c2, tt.c3) VALUES (st.c1, st.c2, st.c3)
WHERE (st.c2 = 'A') ;

MERGE INTO MERGE_T1 tt
USING (
SELECT  c1, c2, c3
FROM MERGE_T2
WHERE c1 >= 99990
AND c1 <= 100090
) st
ON ( tt.c1 = st.c1 )
WHEN NOT MATCHED THEN
INSERT (tt.c1, tt.c2, tt.c3) VALUES (st.c1, st.c2, st.c3)
WHERE (st.c2 = 'A') 
WHEN MATCHED THEN
UPDATE SET tt.c2 = st.c2, tt.c3 = st.c3
DELETE WHERE (tt.c2 = 'A');

CASE2)Only INSERT

MERGE INTO MERGE_T1 tt
USING (
SELECT  c1, c2, c3
FROM MERGE_T2
WHERE c1 >= 99990
AND c1 <= 100090
) st
ON ( tt.c1 = st.c1 )
WHEN NOT MATCHED THEN
INSERT (tt.c1, tt.c2, tt.c3) VALUES (st.c1, st.c2, st.c3)
WHERE (st.c2 = 'A') ;

CASE3)Only UPDATE or UPDATE & DELETE

MERGE INTO MERGE_T1 tt
USING (
SELECT  c1, c2, c3
FROM MERGE_T2
WHERE c1 >= 99990
AND c1 <= 100090
) st
ON ( tt.c1 = st.c1 )
WHEN MATCHED THEN
UPDATE SET tt.c2 = st.c2, tt.c3 = st.c3
DELETE WHERE (tt.c2 = 'A');