- 문법
MERGE INTO table_name alias
USING (table|view|subquery) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET col1 = val1[, col2 = val2…]
WHEN NOT MATCHED THEN
INSERT (컬럼리스트) VALUES (값들....);
MERGE는 UPDATE와 INSERT를 결합한 문장으로 각각의 쓰임새는 다음과 같다.
INTO clause : data가 update 되거나 insert될 table 이름
USING clause : 대상 table의 data와 비교한 후 update 또는 insert할 때 사용할 data의 source.
ON clause : update나 insert를 하게 될 condition으로, 해당 condition을 만족하는 row가 있으면 WHEN MATCHED 이하를 실행하게 되고, 없으면 WHEN NOT MATCHED 이하를 실행하게 된다.
WHEN MATCHED : ON clause의 조건이 TRUE인 row에 수행할 내용
WHEN NOT MATCHED : ON clause의 조건에 맞는 row가 없을 때 수행할 내용
--------------------------------------------- 예
SQL> create table emp_history (
2 empno number(4) not null primary key,
3 mydate date,
4 salary number(7,2));
테이블이 생성되었습니다.
SQL> MERGE INTO emp_history eh
2 USING emp e
3 ON (e.empno = eh.empno)
4 WHEN MATCHED THEN
5 UPDATE SET eh.salary = e.sal
6 WHEN NOT MATCHED THEN
7 INSERT VALUES (e.empno, sysdate, sal);
14 행이 병합되었습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> select * from emp_history;
EMPNO MYDATE SALARY
---------- -------- ----------
7844 04/01/06 1500
7839 04/01/06
7782 04/01/06 2450
7521 04/01/06 1250
7654 04/01/06 1250
7788 04/01/06 3000
7698 04/01/06 2850
7566 04/01/06 2975
7499 04/01/06 1600
7934 04/01/06 1300
7902 04/01/06 3000
7369 04/01/06 800
7876 04/01/06 1100
7900 04/01/06 950
14 개의 행이 선택되었습니다.
===============================================================================
-. 추가하는 데이터가 테이블에 존재하지 않을 때는 INSERT가 수행되고, 이미 데이터가
존재할 경우에는 UPDATE가 수행된다.
-. MERGE문장 작성 순서
1. 새로운 행이 추가되거나 갱신이 될 테이블을 지정한다.
2. 다른 테이블을 참조하여 데이터를 비교한다면 대상 테이블 명을 별칭과 함께 정의한다.
3. 새로운 행을 추가하려고 하는 테이블과 대상 테이블 간에 조인이 필요하면 ON절을
활용 한다.
4. 조인 조건에 의해 만족하는 데이터가 있을 경우엔 UPDATE가 수행되고 만족하는
데이터가 존재하지 않으면 INSERT문이 수행된다.
===============================================================================
또다른 예문
merge 간단한 예제
1. 테이블을 생성한다.
create table table_dest (
id number primary key,
txt varchar2(20)
);
insert into table_dest values (1,'one');
insert into table_dest values (3,'three');
insert into table_dest values (5,'five');
commit;
create table table_source (
id number primary key,
txt varchar2(20)
);
insert into table_source values (2,'TWO');
insert into table_source values (3,'THREE');
commit;
2. merge를 사용하여 처리한다.
merge into
table_dest d
[설명] 처리되는 테이블를 명시한다.
using
table_source s
[설명] 비교할 대상이 여기에 해당되는데.. table, view, sub-query
on
(s.id = d.id)
[설명] 조건을 나열한 만약 여러개일 경우에는 and로 나열하면 됨
when matched then
update set d.txt = s.txt
when not matched then
insert (id, txt) values (s.id, s.txt);
3. 확인함.
select * from table_dest;
[요약정리]
MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
WHEN NOT MATCHED THEN <insert_clause>;
[또 다른 예제]
MERGE INTO SALES_FACT D
USING SALES_JUL01 S
ON (D.TIME_ID = S.TIME_ID
AND D.STORE_ID = S.STORE_ID
AND D.REGION_ID = S.REGION_ID)
WHEN MATCHED THEN
UPDATE
SET d_parts = d_parts + s_parts,
d_sales_amt = d_sales_amt + s_sales_amt,
d_tax_amt = d_tax_amt + s_tax_amt,
d_discount = d_discount + s_discount
WHEN NOT MATCHED THEN
INSERT (D.TIME_ID ,D.STORE_ID ,D.REGION_ID,
D.PARTS ,D.SALES_AMT ,D.TAX_AMT ,D.DISCOUNT)
VALUES (
S.TIME_ID ,S.STORE_ID ,S.REGION_ID,
S.PARTS ,S.SALES_AMT ,S.TAX_AMT ,S.DISCOUNT);
=================================================================================
애플리케이션 로직에서 데이터 유뮤를 검사하고 있으면 , 데이터를 UPDATE 하고
없으면 INSERT 하는 흔한 패턴을 아주 간단하게 쿼리 한문장으로 처리할 수 있게 하는 쿼리가
MERGE Statement 입니다.
예문)
MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1);
오라클 도큐먼트에 있는 예문입니다.
=============================================================================
짱가's 사족 : 한시간여 헤멘끝에 발견한...
key관련절은 merge update불가능
( 키워드 오류가 남 )
MERGE INTO TB_BUSI_MENU x
USING ( SELECT '2005' BUSI_YEAR, '21S000' BUSI_CD, '21S001' DETAIL_BUSI_CD,
'125010' DETAIL_MENU_CD, '2' ORDER_RANK ,
'testUserId' REG_ID, SYSDATE REG_DT, '127.0.0.1' REG_IP, 'Y' DEL_YN
FROM DUAL )
ON ( x.BUSI_YEAR = y.BUSI_YEAR AND BUSI_CD = '21S000' AND DETAIL_BUSI_CD = '21S001' AND DETAIL_MENU_CD = '125010' )
WHEN MATCHED THEN
UPDATE
SET BUSI_YEAR= '2005',BUSI_CD= '21S000', DETAIL_BUSI_CD='21S001',
DETAIL_MENU_CD='125010', ORDER_RANK='2',
UPD_ID='testUserId', UPD_DT=SYSDATE, DEL_YN='Y'
WHEN NOT MATCHED THEN
INSERT (BUSI_YEAR, BUSI_CD, DETAIL_BUSI_CD,
DETAIL_MENU_CD, ORDER_RANK ,
REG_ID, REG_DT, REG_IP, DEL_YN)
VALUES ('2005', '21S000', '21S001',
'125010', '2',
'testUserId', SYSDATE, '127.0.0.1', 'Y' )
( 밑의 문장은 몇개의 update문장을 제외하고 실행성공한 문장 )
그외에 몇가지 다른 이유가 있는 듯함 제외하고 실행하니 update는 되나
insert는 되지 않는 오류가 발생
그래서 dual에서 읽어오는 것 처럼 작성하여 join문을 작성.
MERGE INTO tb_busi_menu x
USING (SELECT '2005' busi_year, '21S000' busi_cd, '21S001' detail_busi_cd,
'125010' detail_menu_cd, '2' order_rank, 'testUserId' reg_id,
SYSDATE reg_dt, '127.0.0.1' reg_ip, 'testUserId' upd_id,
SYSDATE upd_dt, 'Y' del_yn
FROM DUAL) y
ON ( x.busi_year = y.busi_year
AND x.busi_cd = y.busi_cd
AND x.detail_busi_cd = y.detail_busi_cd
AND x.detail_menu_cd = y.detail_menu_cd)
WHEN MATCHED THEN
UPDATE
SET order_rank = y.order_rank, upd_id = y.upd_id,
upd_dt = y.upd_dt, del_yn = y.del_yn
WHEN NOT MATCHED THEN
INSERT (busi_year, busi_cd, detail_busi_cd, detail_menu_cd,
order_rank, reg_id, reg_dt, reg_ip, del_yn)
VALUES (y.busi_year, y.busi_cd, y.detail_busi_cd,
y.detail_menu_cd, y.order_rank, y.reg_id, y.reg_dt,
y.reg_ip, y.del_yn)
Rss Feed