MERGE
MERGE
두 번째 테이블 또는 하위 쿼리의 값을 기반으로 테이블의 값을 삽입, 업데이트, 삭제하는 명령이에요. 두 번째 테이블이 대상 테이블의 새 행(삽입할), 수정된 행(업데이트할), 또는 표시된 행(삭제할)을 포함하는 변경 로그라면 병합이 유용할 수 있어요.
출처: 문서
본문
명령은 다음 경우를 처리하기 위한 의미론(semantics)을 지원해요:
- 일치하는 값(업데이트 및 삭제용).
- 일치하지 않는 값(삽입용).
구문 (Syntax)
MERGE INTO <target_table>
USING <source>
ON <join_expr>
{ matchedClause | notMatchedClause } [ ... ]
여기서:
matchedClause ::=
WHEN MATCHED
[ AND <case_predicate> ]
THEN { UPDATE { ALL BY NAME | SET <col_name> = <expr> [ , <col_name> = <expr> ... ] } | DELETE } [ ... ]
notMatchedClause ::=
WHEN NOT MATCHED
[ AND <case_predicate> ]
THEN INSERT { ALL BY NAME | [ ( <col_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] ) }
파라미터
- target_table — 병합할 테이블을 지정해요.
- source — 대상 테이블과 조인할 테이블 또는 하위 쿼리를 지정해요.
- join_expr — 대상 테이블과 소스를 조인할 표현식을 지정해요.
matchedClause (업데이트 또는 삭제용)
- WHEN MATCHED ... AND case_predicate — true일 때 일치하는 경우를 실행하게 하는 표현식을 선택적으로 지정해요. 기본값: 없음(일치하는 경우가 항상 실행됨).
- WHEN MATCHED ... THEN { UPDATE { ALL BY NAME | SET ... } | DELETE } — 값이 일치할 때 수행할 작업을 지정해요.
ALL BY NAME— 소스의 값으로 대상 테이블의 모든 열을 업데이트해요. 대상 테이블의 각 열은 소스에서 같은 이름을 가진 열의 값으로 업데이트돼요. 대상 테이블과 소스는 모든 열에 대해 같은 수의 열과 같은 이름을 가져야 해요. 그러나 열 순서는 대상 테이블과 소스 간에 다를 수 있어요.SET col_name = expr [ , col_name = expr ... ]— 새 열 값에 해당 표현식(대상 및 소스 관계를 모두 참조할 수 있음)을 사용하여 대상 테이블의 지정된 열을 업데이트해요. 단일 SET 하위 절에서 여러 열을 업데이트할 수 있어요.DELETE— 소스와 일치할 때 대상 테이블의 행을 삭제해요.
notMatchedClause (삽입용)
- WHEN NOT MATCHED ... AND case_predicate — true일 때 불일치하는 경우를 실행하게 하는 표현식을 선택적으로 지정해요. 기본값: 없음(불일치하는 경우가 항상 실행됨).
- WHEN NOT MATCHED ... THEN INSERT { ALL BY NAME | [ ( col_name [ , ... ] ) ] VALUES ( expr [ , ... ] ) } — 값이 일치하지 않을 때 수행할 작업을 지정해요.
ALL BY NAME— 소스의 값으로 대상 테이블의 모든 열을 삽입해요. 대상 테이블의 각 열은 소스에서 같은 이름을 가진 열의 값으로 삽입돼요. 대상 테이블과 소스는 모든 열에 대해 같은 수의 열과 같은 이름을 가져야 해요. 그러나 열 순서는 다를 수 있어요.( col_name [ , ... ] )— 소스의 값으로 삽입될 대상 테이블의 하나 이상의 열을 선택적으로 지정해요. 기본값: 없음(대상 테이블의 모든 열이 삽입됨).VALUES ( expr [ , ... ] )— 삽입된 열 값에 대한 해당 표현식(소스 관계를 참조해야 함)을 지정해요.
사용 메모 (Usage notes)
- 단일 MERGE 문장은 여러 일치 및 불일치 절(즉
WHEN MATCHED ...및WHEN NOT MATCHED ...)을 포함할 수 있어요. - AND 하위 절을 생략하는(기본 동작) 일치 또는 불일치 절은 문장에서 해당 절 유형의 마지막이어야 해요(예:
WHEN MATCHED ...절 뒤에WHEN MATCHED AND ...절이 올 수 없음). 그렇게 하면 도달 불가능한 경우가 되어 오류가 반환돼요.
중복 조인 동작 (Duplicate join behavior)
소스 테이블의 여러 행이 대상 테이블의 단일 행과 일치할 때 결과는 결정적(deterministic)일 수도 있고 비결정적(nondeterministic)일 수도 있어요.
UPDATE 및 DELETE에 대한 비결정적 결과 (Nondeterministic results)
병합이 대상 테이블의 행을 소스의 여러 행과 조인할 때 다음 조인 조건은 비결정적 결과를 생성해요(즉 시스템이 대상 행을 업데이트하거나 삭제하는 데 사용할 소스 값을 결정하지 못함):
- 대상 행이 여러 값으로 업데이트되도록 선택됨(예:
WHEN MATCHED ... THEN UPDATE). - 대상 행이 업데이트 및 삭제 모두로 선택됨(예:
WHEN MATCHED ... THEN UPDATE, WHEN MATCHED ... THEN DELETE).
이 상황에서 병합의 결과는 ERROR_ON_NONDETERMINISTIC_MERGE 세션 파라미터에 지정된 값에 따라 달라져요:
- TRUE(기본값)면 병합이 오류를 반환해요.
- FALSE면 중복 중 하나의 행이 업데이트나 삭제를 수행하도록 선택되며, 선택된 행은 정의되지 않아요.
UPDATE 및 DELETE에 대한 결정적 결과 (Deterministic results)
결정적 병합은 항상 오류 없이 완료돼요. 병합은 각 대상 행에 대해 다음 조건 중 하나 이상을 충족하면 결정적이에요:
- 소스 행 중 하나 이상이
WHEN MATCHED ... THEN DELETE절을 만족하고, 다른 소스 행은 어떤WHEN MATCHED절도 만족하지 않음. - 정확히 하나의 소스 행이
WHEN MATCHED ... THEN UPDATE절을 만족하고, 다른 소스 행은 어떤WHEN MATCHED절도 만족하지 않음.
이렇게 하면 MERGE가 UPDATE 및 DELETE 명령과 의미적으로 동등해져요.
참고: 데이터 소스(즉 소스 테이블 또는 하위 쿼리)의 여러 행이 ON 조건에 따라 대상 테이블과 일치할 때 오류를 피하려면 소스 절에서 GROUP BY를 사용하여 각 대상 행이 소스에서 한 행(최대)과 조인되도록 해요. 다음 예시에서 src가 같은 k 값을 가진 여러 행을 포함한다고 가정해요. 같은 k 값을 가진 대상 행을 업데이트하는 데 어떤 값(v)이 사용될지 모호해요. MAX 함수와 GROUP BY를 사용하면 src의 어떤 v 값이 사용되는지 정확히 명확해져요:
MERGE INTO target
USING (SELECT k, MAX(v) AS v FROM src GROUP BY k) AS b
ON target.k = b.k
WHEN MATCHED THEN UPDATE SET target.v = b.v
WHEN NOT MATCHED THEN INSERT (k, v) VALUES (b.k, b.v);
INSERT에 대한 결정적 결과 (Deterministic results)
결정적 병합은 항상 오류 없이 완료돼요. MERGE 문장에 WHEN NOT MATCHED ... THEN INSERT 절이 포함되고 대상에 일치하는 행이 없으며 소스에 중복 값이 포함되어 있으면, 대상은 소스의 각 복사본에 대해 행의 복사본 하나를 얻게 돼요.
예시 (Examples)
값을 업데이트하는 기본 병합
소스 테이블의 값을 사용하여 대상 테이블의 값을 업데이트하는 기본 병합 예시예요. 두 테이블을 만들고 채워요:
CREATE OR REPLACE TABLE merge_example_target (id INTEGER, description VARCHAR);
INSERT INTO merge_example_target (id, description) VALUES
(10, 'To be updated (this is the old value)');
CREATE OR REPLACE TABLE merge_example_source (id INTEGER, description VARCHAR);
INSERT INTO merge_example_source (id, description) VALUES
(10, 'To be updated (this is the new value)');
테이블의 값을 표시해요:
SELECT * FROM merge_example_target;
+----+---------------------------------------+
| ID | DESCRIPTION |
|----+---------------------------------------|
| 10 | To be updated (this is the old value) |
+----+---------------------------------------+
SELECT * FROM merge_example_source;
+----+---------------------------------------+
| ID | DESCRIPTION |
|----+---------------------------------------|
| 10 | To be updated (this is the new value) |
+----+---------------------------------------+
MERGE 문장을 실행해요:
MERGE INTO merge_example_target
USING merge_example_source
ON merge_example_target.id = merge_example_source.id
WHEN MATCHED THEN
UPDATE SET merge_example_target.description = merge_example_source.description;
+------------------------+
| number of rows updated |
|------------------------|
| 1 |
+------------------------+
대상 테이블의 새 값을 표시해요(소스 테이블은 변경되지 않음):
SELECT * FROM merge_example_target;
+----+---------------------------------------+
| ID | DESCRIPTION |
|----+---------------------------------------|
| 10 | To be updated (this is the new value) |
+----+---------------------------------------+
여러 작업이 있는 기본 병합
INSERT, UPDATE, DELETE 작업의 혼합으로 기본 병합을 수행해요.
두 테이블을 만들고 채워요:
CREATE OR REPLACE TABLE merge_example_mult_target (
id INTEGER,
val INTEGER,
status VARCHAR);
INSERT INTO merge_example_mult_target (id, val, status) VALUES
(1, 10, 'Production'),
(2, 20, 'Alpha'),
(3, 30, 'Production');
CREATE OR REPLACE TABLE merge_example_mult_source (
id INTEGER,
marked VARCHAR,
isnewstatus INTEGER,
newval INTEGER,
newstatus VARCHAR);
INSERT INTO merge_example_mult_source (id, marked, isnewstatus, newval, newstatus) VALUES
(1, 'Y', 0, 10, 'Production'),
(2, 'N', 1, 50, 'Beta'),
(3, 'N', 0, 60, 'Deprecated'),
(4, 'N', 0, 40, 'Production');
테이블의 값을 표시해요:
SELECT * FROM merge_example_mult_target;
+----+-----+------------+
| ID | VAL | STATUS |
|----+-----+------------|
| 1 | 10 | Production |
| 2 | 20 | Alpha |
| 3 | 30 | Production |
+----+-----+------------+
SELECT * FROM merge_example_mult_source;
+----+--------+-------------+--------+------------+
| ID | MARKED | ISNEWSTATUS | NEWVAL | NEWSTATUS |
|----+--------+-------------+--------+------------|
| 1 | Y | 0 | 10 | Production |
| 2 | N | 1 | 50 | Beta |
| 3 | N | 0 | 60 | Deprecated |
| 4 | N | 0 | 40 | Production |
+----+--------+-------------+--------+------------+
다음 병합 예시는 merge_example_mult_target 테이블에서 다음 작업을 수행해요:
- merge_example_mult_source에서 같은 id를 가진 행의 marked 열이 Y이므로 id가 1인 행을 삭제해요.
- merge_example_mult_source에서 같은 행의 isnewstatus가 1로 설정되어 있으므로 id가 2인 행의 val 및 status 값을 같은 id의 행 값으로 업데이트해요.
- merge_example_mult_source의 같은 id 행 값으로 id가 3인 행의 val 값을 업데이트해요. MERGE 문장은 merge_example_mult_source에서 이 행의 isnewstatus가 0으로 설정되어 있으므로 merge_example_mult_target의 status 값을 업데이트하지 않아요.
- 행이 merge_example_mult_source에 존재하고 merge_example_mult_target에 일치하는 행이 없으므로 id가 4인 행을 삽입해요.
MERGE INTO merge_example_mult_target
USING merge_example_mult_source
ON merge_example_mult_target.id = merge_example_mult_source.id
WHEN MATCHED AND merge_example_mult_source.marked = 'Y'
THEN DELETE
WHEN MATCHED AND merge_example_mult_source.isnewstatus = 1
THEN UPDATE SET val = merge_example_mult_source.newval, status = merge_example_mult_source.newstatus
WHEN MATCHED
THEN UPDATE SET val = merge_example_mult_source.newval
WHEN NOT MATCHED
THEN INSERT (id, val, status) VALUES (
merge_example_mult_source.id,
merge_example_mult_source.newval,
merge_example_mult_source.newstatus);
+-------------------------+------------------------+------------------------+
| number of rows inserted | number of rows updated | number of rows deleted |
|-------------------------+------------------------+------------------------|
| 1 | 2 | 1 |
+-------------------------+------------------------+------------------------+
병합 결과를 보려면 merge_example_mult_target 테이블의 값을 표시해요:
SELECT * FROM merge_example_mult_target ORDER BY id;
+----+-----+------------+
| ID | VAL | STATUS |
|----+-----+------------|
| 2 | 50 | Beta |
| 3 | 60 | Production |
| 4 | 40 | Production |
+----+-----+------------+
ALL BY NAME을 사용한 병합
소스 테이블의 값을 사용하여 대상 테이블의 값을 삽입하고 업데이트하는 병합 예시예요. 예시는 WHEN MATCHED ... THEN ALL BY NAME 및 WHEN NOT MATCHED ... THEN ALL BY NAME 하위 절로 병합이 모든 열에 적용됨을 지정해요.
같은 수의 열과 같은 열 이름을 가지지만 두 열의 순서가 다른 두 테이블을 만들어요:
CREATE OR REPLACE TABLE merge_example_target_all (
id INTEGER,
x INTEGER,
y VARCHAR);
CREATE OR REPLACE TABLE merge_example_source_all (
id INTEGER,
y VARCHAR,
x INTEGER);
테이블을 채워요:
INSERT INTO merge_example_target_all (id, x, y) VALUES
(1, 10, 'Skiing'),
(2, 20, 'Snowboarding');
INSERT INTO merge_example_source_all (id, y, x) VALUES
(1, 'Skiing', 10),
(2, 'Snowboarding', 25),
(3, 'Skating', 30);
테이블의 값을 표시해요:
SELECT * FROM merge_example_target_all;
+----+----+--------------+
| ID | X | Y |
|----+----+--------------|
| 1 | 10 | Skiing |
| 2 | 20 | Snowboarding |
+----+----+--------------+
SELECT * FROM merge_example_source_all;
+----+--------------+----+
| ID | Y | X |
|----+--------------+----|
| 1 | Skiing | 10 |
| 2 | Snowboarding | 25 |
| 3 | Skating | 30 |
+----+--------------+----+
MERGE 문장을 실행해요:
MERGE INTO merge_example_target_all
USING merge_example_source_all
ON merge_example_target_all.id = merge_example_source_all.id
WHEN MATCHED THEN
UPDATE ALL BY NAME
WHEN NOT MATCHED THEN
INSERT ALL BY NAME;
+-------------------------+------------------------+
| number of rows inserted | number of rows updated |
|-------------------------+------------------------|
| 1 | 2 |
+-------------------------+------------------------+
대상 테이블의 새 값을 표시해요:
SELECT *
FROM merge_example_target_all
ORDER BY id;
+----+----+--------------+
| ID | X | Y |
|----+----+--------------|
| 1 | 10 | Skiing |
| 2 | 25 | Snowboarding |
| 3 | 30 | Skating |
+----+----+--------------+
소스 중복이 있는 병합
소스에 중복 값이 있고 대상에 일치하는 값이 없는 병합을 수행해요. 소스 레코드의 모든 복사본이 대상에 삽입돼요.
두 테이블을 잘라내고 중복을 포함한 새 행을 소스 테이블에 로드해요:
TRUNCATE table merge_example_target;
TRUNCATE table merge_example_source;
INSERT INTO merge_example_source (id, description) VALUES
(50, 'This is a duplicate in the source and has no match in target'),
(50, 'This is a duplicate in the source and has no match in target');
merge_example_source 테이블의 값을 표시해요:
SELECT * FROM merge_example_source;
+----+--------------------------------------------------------------+
| ID | DESCRIPTION |
|----+--------------------------------------------------------------|
| 50 | This is a duplicate in the source and has no match in target |
| 50 | This is a duplicate in the source and has no match in target |
+----+--------------------------------------------------------------+
MERGE 문장을 실행해요:
MERGE INTO merge_example_target
USING merge_example_source
ON merge_example_target.id = merge_example_source.id
WHEN MATCHED THEN
UPDATE SET merge_example_target.description = merge_example_source.description
WHEN NOT MATCHED THEN
INSERT (id, description) VALUES
(merge_example_source.id, merge_example_source.description);
+-------------------------+------------------------+
| number of rows inserted | number of rows updated |
|-------------------------+------------------------|
| 2 | 0 |
+-------------------------+------------------------+
대상 테이블의 새 값을 표시해요:
SELECT * FROM merge_example_target;
+----+--------------------------------------------------------------+
| ID | DESCRIPTION |
|----+--------------------------------------------------------------|
| 50 | This is a duplicate in the source and has no match in target |
| 50 | This is a duplicate in the source and has no match in target |
+----+--------------------------------------------------------------+
결정적 및 비결정적 결과가 있는 병합
비결정적 및 결정적 결과를 생성하는 조인을 사용하여 레코드를 병합해요.
두 테이블을 만들고 채워요:
CREATE OR REPLACE TABLE merge_example_target_orig (k NUMBER, v NUMBER);
INSERT INTO merge_example_target_orig VALUES (0, 10);
CREATE OR REPLACE TABLE merge_example_src (k NUMBER, v NUMBER);
INSERT INTO merge_example_src VALUES (0, 11), (0, 12), (0, 13);
다음 예시에서 병합을 수행하면 여러 업데이트가 서로 충돌해요. ERROR_ON_NONDETERMINISTIC_MERGE 세션 파라미터가 true로 설정되어 있으면 MERGE 문장이 오류를 반환해요. 그렇지 않으면 MERGE 문장은 중복 행(정의되지 않은 행) 중 하나의 값(예: 11, 12, 13)으로 merge_example_target_clone.v를 업데이트해요:
CREATE OR REPLACE TABLE merge_example_target_clone
CLONE merge_example_target_orig;
MERGE INTO merge_example_target_clone
USING merge_example_src
ON merge_example_target_clone.k = merge_example_src.k
WHEN MATCHED THEN UPDATE SET merge_example_target_clone.v = merge_example_src.v;
업데이트와 삭제가 서로 충돌해요. ERROR_ON_NONDETERMINISTIC_MERGE 세션 파라미터가 true로 설정되어 있으면 MERGE 문장이 오류를 반환해요. 그렇지 않으면 MERGE 문장은 행을 삭제하거나 중복 행(정의되지 않은 행) 중 하나의 값(예: 12, 13)으로 merge_example_target_clone.v를 업데이트해요:
CREATE OR REPLACE TABLE merge_example_target_clone
CLONE merge_example_target_orig;
MERGE INTO merge_example_target_clone
USING merge_example_src
ON merge_example_target_clone.k = merge_example_src.k
WHEN MATCHED AND merge_example_src.v = 11 THEN DELETE
WHEN MATCHED THEN UPDATE SET merge_example_target_clone.v = merge_example_src.v;
여러 삭제는 서로 충돌하지 않아요. 어떤 절과도 일치하지 않는 조인된 값은 삭제를 방해하지 않아요(merge_example_src.v = 13). MERGE 문장은 성공하고 대상 행이 삭제돼요:
CREATE OR REPLACE TABLE target CLONE merge_example_target_orig;
MERGE INTO merge_example_target_clone
USING merge_example_src
ON merge_example_target_clone.k = merge_example_src.k
WHEN MATCHED AND merge_example_src.v
어떤 절과도 일치하지 않는 조인된 값은 업데이트를 방해하지 않아요(merge_example_src.v = 12, 13). MERGE 문장은 성공하고 대상 행이 target.v = 11로 설정돼요:
CREATE OR REPLACE TABLE merge_example_target_clone CLONE target_orig;
MERGE INTO merge_example_target_clone
USING merge_example_src
ON merge_example_target_clone.k = merge_example_src.k
WHEN MATCHED AND merge_example_src.v = 11
THEN UPDATE SET merge_example_target_clone.v = merge_example_src.v;
소스 절에서 GROUP BY를 사용하여 각 대상 행이 소스의 한 행과 조인되도록 해요:
CREATE OR REPLACE TABLE merge_example_target_clone CLONE merge_example_target_orig;
MERGE INTO merge_example_target_clone
USING (SELECT k, MAX(v) AS v FROM merge_example_src GROUP BY k) AS b
ON merge_example_target_clone.k = b.k
WHEN MATCHED THEN UPDATE SET merge_example_target_clone.v = b.v
WHEN NOT MATCHED THEN INSERT (k, v) VALUES (b.k, b.v);
DATE 값을 기반으로 한 병합
다음 예시에서 members 테이블은 이름, 주소, 로컬 체육관에 지불한 현재 회비(members.fee)를 저장해요. signup 테이블은 각 회원의 가입 날짜(signup.date)를 저장해요. MERGE 문장은 무료 체험 기간이 만료된 지 30일 이상 지난 회원에게 표준 $40 요금을 적용해요:
MERGE INTO members m
USING (SELECT id, date
FROM signup
WHERE DATEDIFF(day, CURRENT_DATE(), signup.date::DATE)