오라클 MERGE 사용하기 (update, insert 동시에)

출처: http://zinlee.tistory.com/185

오라클 MERGE 사용하기 (update, insert 동시에)

개발을 하다보면 update와 insert문 둘중에 한가지만 실행해야 할 때가 있다.

예로 해당 Data가 있으면 update해주고 없으면 insert를 해야하는 경우가 그렇다.

이럴 경우에는 오라클의 MERGE 구문을 이용해서 처리하면 간단하다.

DUAL ON 뒤의 조건에 해당하는 Data가 있으면

WHEN MATCHED THEN의 구문을 통해 UPDATE문이 실행이 되고,

해당하는 Data가 없다면

WHEN NOT MATCHED THEN 구문을 통해 INSERT문이 실행이 된다.

◇ FORMAT
─────────────────────────────────────────────
MERGE INTO dest_table_name [alias]
USING (source_table_name or view or subquery) [alias]

ON (join condition)
WHEN MATCHED THEN 
UPDATE SET col1 = value1[, col2 = value2…]
WHEN NOT MATCHED THEN 
INSERT [(col1, col2, ... coln)] VALUES(value1, value2 ... valuen)

desc_table_name ....... UPSERT¹ 하고자 하는 테이블명

alias ......................... 조인조건, UPDATE, INSERT등레 사용될 Alias명

source_table_name .... UPSERT할 값이 들어있는 테이블명

view ......................... UPSERT할 값이 들어있는 뷰테이블명

subquery .................. UPSERT할 값을 SELECT 한 쿼리문장

join condition ............ UPSERT하기위한 조건 (WHERE절에 해당한다)

지정한 join condition에 의하여 그 값이 일치하면 UPDATE를 일치하지 않으면 INSERT를 수행한다.

※ ON 절에 기술된 컬럼이 WHEN MATCHED THEN 다음에 오는 UPDATE 문장에는 올수없다

즉, KEY에 해당하는 부분일 수 있으므로 조건에 해당된 컬럼이 UPDATE되는 것을 방지한다.

─────────────────────────────────────────────

MERGE INTO SWTB_SAFETY_WORK_SUB USING DUAL ON (REQ_NO = ? AND WORK_NO = ?)

WHEN MATCHED THEN 

UPDATE SET 

CHECK_TIME = ?, 

CHECK_PERSON = ?, 

CHECK_RESULT = ? 

WHEN NOT MATCHED THEN

INSERT (REQ_NO, WORK_NO, CHECK_TIME, CHECK_PERSON, CHECK_RESULT, CHECK_EMP_NO)

VALUES(?, ?, ?, ?, ?, ?)




다른 예시--------------------------

MERGE INTO TABLE1 A
USING
(
SELECT

:SEQ_NO AS seq_no, :KOR_NAME AS kor_name, :JOB AS job, :STATUS AS status

FROM dual

) B
ON
(
A.SEQ_NO = B.SEQ_NO

)
WHEN MATCHED THEN
UPDATE SET A.JOB = B.JOB
WHEN NOT MATCHED THEN
INSERT VALUES (B.SEQ_NO, B.KOR_NAME, B.JOB, B.STATUS)

  

select 문 이용하는 케이스

출처: 즐거움을 찾자 Find Fun!! :: 오라클 MERGE INTO - 한번에 INSERT, UPDATE 하기

아래는 실제로 사용했던 예. 테이블 명은 임의로 바꿨다. 소스 테이블에 올 내용은 아래처럼 SELECT로 구성해도 된다.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

merge into target_table tt     using         (             select                 te.te_cd tt_cd                 ,te.description tt_disp                 ,te.jt_no jt_no                 ,decode(te.is_flag, 'normal', 'Y', 'N') is_flag             from                 temp_table te             left join join_table jt                 on te.te_no=jt.jt_no             where                 1=1         ) so     on (tt.tt_cd=so.tt_cd and tt.jt_no=so.jt_no)           when matched then         update set             tt.is_flag=so.is_flag             ,tt.tt_disp=so.tt_disp         when not matched then             insert (tt_no, tt_cd, tt_disp, jt_no, is_flag, reg_date)             values(                 sq_target_table.nextval                 , so.tt_cd                 , so.tt_disp                 , so.jt_no                 , so.is_flag                 , sysdate             );