[MSSQL] MERGE INTO - UPDATE or INSERT

조건에 맞는 데이터가 있으면 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)