MERGE¶
MERGE 문은 하나의 원본으로부터 행들을 선택하여 하나의 테이블 또는 뷰로 갱신이나 삽입을 수행하기 위해 사용되며, 대상 테이블 또는 뷰에 갱신할지 또는 삽입할지 결정하는 조건을 지정할 수 있다. MERGE 문은 결정적 문장(deterministic statement)으로, 하나의 문장 내에서 대상 테이블의 같은 행을 여러 번 갱신할 수 없다.
MERGE [<merge_hint>] INTO <target> [[AS] <alias>]
USING <source> [[AS] <alias>]
ON <join_condition>
[ <merge_update_clause> ]
[ <merge_insert_clause> ]
<merge_update_clause> ::=
WHEN MATCHED THEN UPDATE
SET <col = expr> [,<col = expr>,...] [WHERE <update_condition>]
[DELETE WHERE <delete_condition>]
<merge_insert_clause> ::=
WHEN NOT MATCHED THEN INSERT [(<attributes_list>)] VALUES (<expr_list>) [WHERE <insert_condition>]
<merge_hint> ::=
/*+ [ USE_UPDATE_IDX (<update_index_list>) ] [ USE_INSERT_IDX (<insert_index_list>) ] */
<target>: 갱신하거나 삽입할 대상 테이블. 여러 개의 테이블 또는 뷰가 될 수 있으며 table-name@server-name처럼 원격 테이블도 지정할 수 있다.
<source>: 데이터를 가져올 원본 테이블. 하나의 테이블 또는 뷰가 될 수 있으며, 부질의(subquery)도 가능하다. target이 원격 테이블인 경우, source도 targe과 동일한 서버로 지정된 원격 테이블이거나 뷰이어야 한다. 혹은, target에 지정된 서버가 포함된 부질의이어야 한다.
<join_condition>: 갱신할 조건을 명시한다.
<merge_update_clause>: <join_condition> 조건이 TRUE이면 대상 테이블의 새로운 칼럼 값들을 지정한다.
UPDATE 절이 실행되면, <target>에 정의된 UPDATE 트리거가 활성화된다.
<col>: 업데이트할 칼럼은 반드시 대상 테이블의 칼럼이어야 한다.
<expr>: <source>와 <target>의 칼럼들을 포함하는 표현식도 가능하다. 또는 DEFAULT 키워드도 될 수 있다. <expr>은 집계 함수를 포함할 수 없다.
<update_condition>: 특정 조건이 TRUE일 때만 UPDATE 연산을 수행한다. 조건은 원본 테이블과 대상 테이블 둘 다 참조할 수 있다. 이 조건을 만족하지 않는 행들은 업데이트하지 않는다.
<delete_condition>: 갱신 이후 삭제할 대상을 지정한다. 이때 DELETE 조건은 갱신된 결과 값을 가지고 수행된다. DELETE 절이 실행되면 <target>에 정의된 DELETE 트리거가 활성화된다.
ON 조건 절에서 참조된 칼럼은 업데이트할 수 없다.
뷰를 업데이트할 때에는 DEFAULT 를 명시할 수 없다.
<merge_insert_clause>: <join_condition> 조건이 FALSE이면 대상 테이블의 칼럼으로 값을 삽입한다.
INSERT 절이 실행되면, <target>에 정의된 INSERT 트리거들이 활성화된다.
<insert_condition>: 지정한 조건이 TRUE일 때 삽입 연산을 실행한다. <source>의 칼럼만 조건에 포함할 수 있다.
<attribute_list>: <target>에 삽입될 칼럼들이다.
<expr_list>: 상수 필터 조건은 모든 원본 테이블의 행들을 대상 테이블에 삽입하는 데 사용될 수 있다. 상수 필터 조건의 예로 ON (1=1)과 같은 것이 있다.
<merge_update_clause>만 지정하거나 <merge_update_clause>와 함께 지정할 수 있다. 둘 다 명시한다면 순서는 바뀌어도 된다.
<merge_hint>: MERGE 문의 인덱스 힌트
USE_UPDATE_IDX (<update_index_list>): MERGE 문의 UPDATE 절에서 사용되는 인덱스 힌트. update_index_list에 UPDATE 절을 수행할 때 사용할 인덱스 이름을 나열한다. <join_condition>과 <update_condition>에 해당 힌트가 적용된다.
USE_INSERT_IDX (<insert_index_list>): MERGE 문의 INSERT 절에서 사용되는 인덱스 힌트. insert_index_list에 INSERT 절을 수행할 때 사용할 인덱스 이름을 나열한다. <join_condition>에 해당 힌트가 적용된다.
MERGE 문을 실행하기 위해서는 원본 테이블에 대해 SELECT 권한을 가져야 하며, 대상 테이블에 대해 UPDATE 절이 포함되어 있으면 UPDATE 권한, DELETE 절이 포함되어 있으면 DELETE 권한, INSERT 절이 포함되어 있으면 INSERT 권한을 가져야 한다.
다음은 source_table의 값을 target_table에 합치는 예이다.
-- source_table
CREATE TABLE source_table (a INT, b INT, c INT);
INSERT INTO source_table VALUES (1, 1, 1);
INSERT INTO source_table VALUES (1, 3, 2);
INSERT INTO source_table VALUES (2, 4, 5);
INSERT INTO source_table VALUES (3, 1, 3);
-- target_table
CREATE TABLE target_table (a INT, b INT, c INT);
INSERT INTO target_table VALUES (1, 1, 4);
INSERT INTO target_table VALUES (1, 2, 5);
INSERT INTO target_table VALUES (1, 3, 2);
INSERT INTO target_table VALUES (3, 1, 6);
INSERT INTO target_table VALUES (5, 5, 2);
MERGE INTO target_table tt USING source_table st
ON (st.a=tt.a AND st.b=tt.b)
WHEN MATCHED THEN UPDATE SET tt.c=st.c
DELETE WHERE tt.c = 1
WHEN NOT MATCHED THEN INSERT VALUES (st.a, st.b, st.c);
-- the result of above query
SELECT * FROM target_table;
a b c
=======================================
1 2 5
1 3 2
3 1 3
5 5 2
2 4 5
위의 예에서 source_table의 칼럼 a, b와 target_table의 칼럼 a, b의 값이 같은 경우 target_table의 칼럼 c를 source_table의 칼럼 c값으로 갱신하고, 그렇지 않은 경우 source_table의 레코드 값을 target_table에 삽입하는 예이다. 단, 갱신된 레코드에서 target_table의 칼럼 c의 값이 1이면 해당 레코드는 삭제된다.
다음은 학생들에게 줄 보너스 점수 테이블(bonus)의 레코드를 정리할 때 MERGE 문을 이용하는 예제이다.
CREATE TABLE bonus (std_id INT, addscore INT);
CREATE INDEX i_bonus_std_id ON bonus (std_id);
INSERT INTO bonus VALUES (1,10);
INSERT INTO bonus VALUES (2,10);
INSERT INTO bonus VALUES (3,10);
INSERT INTO bonus VALUES (4,10);
INSERT INTO bonus VALUES (5,10);
INSERT INTO bonus VALUES (6,10);
INSERT INTO bonus VALUES (7,10);
INSERT INTO bonus VALUES (8,10);
INSERT INTO bonus VALUES (9,10);
INSERT INTO bonus VALUES (10,10);
CREATE TABLE std (std_id INT, score INT);
CREATE INDEX i_std_std_id ON std (std_id);
CREATE INDEX i_std_std_id_score ON std (std_id, score);
INSERT INTO std VALUES (1,60);
INSERT INTO std VALUES (2,70);
INSERT INTO std VALUES (3,80);
INSERT INTO std VALUES (4,35);
INSERT INTO std VALUES (5,55);
INSERT INTO std VALUES (6,30);
INSERT INTO std VALUES (7,65);
INSERT INTO std VALUES (8,65);
INSERT INTO std VALUES (9,70);
INSERT INTO std VALUES (10,22);
INSERT INTO std VALUES (11,67);
INSERT INTO std VALUES (12,20);
INSERT INTO std VALUES (13,45);
INSERT INTO std VALUES (14,30);
MERGE INTO bonus t USING (SELECT * FROM std WHERE score < 40) s
ON t.std_id = s.std_id
WHEN MATCHED THEN
UPDATE SET t.addscore = t.addscore + s.score * 0.1
WHEN NOT MATCHED THEN
INSERT (t.std_id, t.addscore) VALUES (s.std_id, 10 + s.score * 0.1) WHERE s.score <= 30;
SELECT * FROM bonus ORDER BY 1;
std_id addscore
==========================
1 10
2 10
3 10
4 14
5 10
6 13
7 10
8 10
9 10
10 12
12 12
14 13
위의 예에서 원본 테이블은 score가 40 미만인 std 테이블의 레코드 집합이고, 대상 테이블은 bonus이다. UPDATE 절에서 점수(std.score)가 40점 미만인 학생 번호(std_id)는 4, 6, 10, 12, 14이고 이들 중 보너스 테이블(bonus)에 있는 4, 6, 10번에게는 기존 보너스 점수(bonus.addscore)에 자신의 점수의 10%를 추가로 부여한다. INSERT 절에서는 보너스 테이블에 없는 12, 14번에게 10점과 자신의 점수의 10%를 추가로 부여한다.
다음은 MERGE 문에 인덱스 힌트를 사용하는 예이다.
CREATE TABLE target (i INT, j INT);
CREATE TABLE source (i INT, j INT);
INSERT INTO target VALUES (1,1),(2,2),(3,3);
INSERT INTO source VALUES (1,11),(2,22),(4,44),(5,55),(7,77),(8,88);
CREATE INDEX i_t_i ON target(i);
CREATE INDEX i_t_ij ON target(i,j);
CREATE INDEX i_s_i ON source(i);
CREATE INDEX i_s_ij ON source(i,j);
MERGE /*+ RECOMPILE USE_UPDATE_IDX(i_s_ij) USE_INSERT_IDX(i_t_ij, i_t_i) */
INTO target t USING source s ON t.i=s.i
WHEN MATCHED THEN UPDATE SET t.j=s.j WHERE s.i <> 1
WHEN NOT MATCHED THEN INSERT VALUES (i,j);
MERGE 문을 실행하기 위해서는 원본 테이블에 대해 SELECT 권한을 가져야 하며, 대상 테이블에 대해 UPDATE 절이 포함되어 있으면 UPDATE 권한, DELETE 절이 포함되어 있으면 DELETE 권한, INSERT 절이 포함되어 있으면 INSERT 권한을 가져야 한다.
다음은 원격 서버의 source_table 값을 원격 서버의 target_table에 합치는 예이다.
-- at remote server
-- source_table
CREATE TABLE source_table (a INT, b INT, c INT);
CREATE TABLE target_table (a INT, b INT, c INT);
-- at local server
-- source table
INSERT INTO source_table@srv1 VALUES (1, 1, 1);
INSERT INTO source_table@srv1 VALUES (1, 3, 2);
INSERT INTO source_table@srv1 VALUES (2, 4, 5);
INSERT INTO source_table@srv1 VALUES (3, 1, 3);
-- target_table
INSERT INTO target_table@srv1 VALUES (1, 1, 4);
INSERT INTO target_table@srv1 VALUES (1, 2, 5);
INSERT INTO target_table@srv1 VALUES (1, 3, 2);
INSERT INTO target_table@srv1 VALUES (3, 1, 6);
INSERT INTO target_table@srv1 VALUES (5, 5, 2);
MERGE INTO target_table@srv1 tt USING source_table@srv1 st
ON (st.a=tt.a AND st.b=tt.b)
WHEN MATCHED THEN UPDATE SET tt.c=st.c
DELETE WHERE tt.c = 1
WHEN NOT MATCHED THEN INSERT VALUES (st.a, st.b, st.c);
-- the result of above query
SELECT * FROM target_table@srv1;
a b c
=======================================
1 2 5
1 3 2
3 1 3
5 5 2
2 4 5
다음은 원격 서버의 source_table 값을 로컬 서버의 target_table에 합치는 예이다.
-- at remote server
-- source_table
CREATE TABLE source_table (a INT, b INT, c INT);
-- at local server
CREATE TABLE target_table (a INT, b INT, c INT);
-- source table insert
INSERT INTO source_table@srv1 VALUES (1, 1, 1);
INSERT INTO source_table@srv1 VALUES (1, 3, 2);
INSERT INTO source_table@srv1 VALUES (2, 4, 5);
INSERT INTO source_table@srv1 VALUES (3, 1, 3);
-- target_table insert
INSERT INTO target_table VALUES (1, 1, 4);
INSERT INTO target_table VALUES (1, 2, 5);
INSERT INTO target_table VALUES (1, 3, 2);
INSERT INTO target_table VALUES (3, 1, 6);
INSERT INTO target_table VALUES (5, 5, 2);
MERGE INTO target_table tt USING source_table@srv1 st
ON (st.a=tt.a AND st.b=tt.b)
WHEN MATCHED THEN UPDATE SET tt.c=st.c
DELETE WHERE tt.c = 1
WHEN NOT MATCHED THEN INSERT VALUES (st.a, st.b, st.c);
-- the result of above query
SELECT * FROM target_table;
a b c
=======================================
1 2 5
1 3 2
3 1 3
5 5 2
2 4 5
Warning
아래의 예처럼 target이 remote이고 source가 local인 경우는 에러가 발생한다.
MERGE INTO target_table@srv1 tt USING source_table st
ON (st.a=tt.a AND st.b=tt.b)
WHEN MATCHED THEN UPDATE SET tt.c=st.c
DELETE WHERE tt.c = 1
WHEN NOT MATCHED THEN INSERT VALUES (st.a, st.b, st.c);
또한 아래의 예처럼 target과 source가 각각 다른 서버인 경우도 에러가 발생한다.
MERGE INTO target_table@srv1 tt USING source_table@srv2 st
ON (st.a=tt.a AND st.b=tt.b)
WHEN MATCHED THEN UPDATE SET tt.c=st.c
DELETE WHERE tt.c = 1
WHEN NOT MATCHED THEN INSERT VALUES (st.a, st.b, st.c);