[ORACLE] MERGE INTO ~ USING ON

테이블에 데이터가 존재한다면 -> UPDATE

테이블에 데이터가 없다면 -> INSERT

 

이 2개를 한번에 만족할수있는 오라클 문법이  ~>  MERGE INTO ~ USING ON  이다!!

 

 

문법

MERGE INTO 변경 테이블
     USING (데이터)
        ON (조건)
        
WHEN MATCHED THEN
       UPDATE SET 컬럼1 = 값1, 컬럼2 = 값2, ...
     DELETE WHERE (조건)
 
WHEN NOT MATCHED THEN 
               INSERT (컬럼1, 컬럼2, ...) VALUES (값1, 값2, ...)
                WHERE 조건;

 

 

에제

MERGE INTO TB_CM_USR_ATHT_L T1
		using (select #{usrId} USR_ID,
					  #{athtCd} ATHT_CD
				 from dual) T2
		   ON (T1.USR_ID = T2.USR_ID
		  AND  T1.ATHT_CD = T2.ATHT_CD )
	     WHEN NOT MATCHED THEN
	   INSERT (USR_ID, 
	   		   ATHT_CD, 
	   		   UPR_ATHT_CD,
	   		   USE_YN,
	   		   FIRST_REG_DT,
	   		   FIRST_RGSN_ID,
	   		   LAST_CHG_DT,
	   		   LAST_CHGR_ID)
	   values (#{usrId}, 
	   		   #{athtCd}, 
	   		   #{uprAthtCd},
	   		   'Y',
	   		   SYSDATE,
	   		   #{systemId},
	   		   SYSDATE,
	   		   #{systemId})