조건에 맞는 데이터가 있으면 UPDATE 수행, 없으면 INSERT수행하도록 하기
--CASE 1: 기본형
MERGE INTO TABLE_NAME
USING DUAL
ON (A = 1 AND B = 2)
WHEN MATCHED THEN
UPDATE SET COL1 = 1,
COL2 = 2
WHEN NOT MATCHED THEN
INSERT (COL1, COL2)
VALUES (1, 2)
--CASE 2: USING 안에 SELECT문
MERGE INTO SYS_USER A
USING DUAL
ON (A.ORG_ID = #{orgId}
WHEN MATCHED THEN
UPDATE SET A.USER_NM = #{userNm},
A.RMK = #{rmk},
A.TEL_NO = #{telNo},
A.EDIT_DT = SYSDATE
WHEN NOT MATCHED THEN
INSERT (USER_ID, USER_NM, TEL_NO, RMK, EDIT_DT, INS_DT)
VALUES (B.USER_ID, #{userNm}, #{telNo}, #{rmk}, SYSDATE, SYSDATE)
또는
MERGE INTO SYS_USER A
USING (SELECT USER_ID, ORG_ID, COPR_ID
FROM SYS_USER_DTL
WHERE ORG_ID = #{orgId}) B
ON (A.USER_ID = B.USER_ID)
WHEN MATCHED THEN
UPDATE SET A.USER_NM = #{userNm},
A.TEL_NO = B.TEL_NO,
A.RMK = #{rmk},
A.EDIT_DT = SYSDATE
WHEN NOT MATCHED THEN
INSERT (USER_ID, USER_NM, TEL_NO, RMK, EDIT_DT, INS_DT)
VALUES (B.USER_ID, #{userNm}, B.TEL_NO, #{rmk}, SYSDATE, SYSDATE)
