MERGE¶
한 테이블에 있는 값을 두 번째의 다른 테이블이나 하위 쿼리에 있는 값을 기준으로 삽입, 업데이트, 삭제합니다. 이는 두 번째 테이블이 대상 테이블의 (삽입되는) 새 행, (업데이트되는) 수정된 행 및/또는 (삭제되는) 표시된 행을 포함하는 변경 로그인 경우에 유용할 수 있습니다.
이 명령은 다음과 같은 사례를 처리하기 위한 의미 체계를 지원합니다.
일치하는 값(업데이트 및 삭제의 경우).
일치하지 않는 값(삽입의 경우).
구문¶
MERGE INTO <target_table> USING <source> ON <join_expr> { matchedClause | notMatchedClause } [ ... ]
여기서:
matchedClause ::= WHEN MATCHED [ AND <case_predicate> ] THEN { UPDATE SET <col_name> = <expr> [ , <col_name2> = <expr2> ... ] | DELETE } [ ... ]notMatchedClause ::= WHEN NOT MATCHED [ AND <case_predicate> ] THEN INSERT [ ( <col_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] )
매개 변수¶
target_table병합할 테이블을 지정합니다.
source대상 테이블과 조인할 테이블 또는 하위 쿼리를 지정합니다.
join_expr대상 테이블과 원본을 조인할 식을 지정합니다.
matchedClause (업데이트 또는 삭제의 경우)¶
WHEN MATCHED ... THEN UPDATE <col_name> = <expr> | DELETE값이 일치할 때 수행할 작업을 지정합니다.
AND case_predicate참일 때 일치하는 케이스가 실행되도록 하는 식을 선택적으로 지정합니다.
기본값: 값 없음(일치하는 케이스가 항상 실행됨)
SET col_name = expr[ … ]업데이트하거나 삽입할 대상 테이블 내의 열과 새 열 값에 대응되는 식(대상 및 원본 관계를 모두 참조할 수 있음)을 지정합니다.
단일
SET하위 절에서 업데이트/삭제할 여러 개의 열을 지정할 수 있습니다.
notMatchedClause (삽입의 경우)¶
WHEN NOT MATCHED ... THEN INSERT값이 일치하지 않을 때 수행할 작업을 지정합니다.
AND case_predicate참일 때 일치하지 않는 케이스가 실행되도록 하는 식을 선택적으로 지정합니다.
기본값: 값 없음(일치하지 않는 케이스가 항상 실행됨)
( col_name [ , ... ] )업데이트하거나 삽입할 대상 테이블 내에서 하나 이상의 열을 선택적으로 지정합니다.
기본값: 값 없음(대상 테이블 내의 모든 열이 업데이트되거나 삽입됨)
VALUES ( expr [ , ... ] )삽입된 열 값에 대응되는 식을 지정합니다(원본 관계를 참조해야 함).
사용법 노트¶
단일 MERGE 문은 일치하는 절과 일치하지 않는 절(즉,
WHEN MATCHED ...및WHEN NOT MATCHED ...)을 여러 개 포함할 수 있습니다.AND하위 절(기본 동작)을 생략하는 일치하는 절 또는 일치하지 않는 절은 문에서 해당 절 유형의 마지막 절이어야 합니다(예:WHEN MATCHED ...절 다음에WHEN MATCHED AND ...절이 올 수 없음). 그렇게 하면 연결할 수 없는 경우가 생겨 오류가 반환됩니다.
중복 조인 동작¶
UPDATE와 DELETE에 대한 비결정적 결과¶
병합이 원본 테이블의 여러 행에 대해 대상 테이블의 행을 조인하면, 다음과 같은 조인 조건이 비결정적 결과를 생성합니다(즉, 시스템이 대상 행을 업데이트하거나 삭제하는 데 사용할 원본 값을 결정할 수 없음).
다중 값으로 업데이트될 대상 행이 선택됩니다(예:
WHEN MATCHED ... THEN UPDATE).업데이트될 대상 행과 삭제될 대상 행이 선택됩니다(예:
WHEN MATCHED ... THEN UPDATE,WHEN MATCHED ... THEN DELETE).
이 상황에서, 병합 결과는 ERROR_ON_NONDETERMINISTIC_MERGE 세션 매개 변수에 대해 지정된 값에 따라 다릅니다.
TRUE(기본값)인 경우, 병합이 오류를 반환합니다.
FALSE인 경우 중복 항목 중 한 행을 선택하여 업데이트 또는 삭제를 수행하며, 선택한 행은 정의되지 않습니다.
UPDATE와 DELETE에 대한 결정적 결과¶
결정적 병합은 항상 오류 없이 완료됩니다. 병합은 각 대상 행에 대해 다음 조건을 충족하는 경우에 결정적입니다.
하나 이상의 원본 행이
WHEN MATCHED ... THEN DELETE절을 충족하고 다른 어떤 원본 행도WHEN MATCHED절을 충족하지 않습니다.OR
정확히 한 개의 원본 행이
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에 대한 결정적 결과¶
결정적 병합은 항상 오류 없이 완료됩니다.
MERGE가 WHEN NOT MATCHED ... THEN INSERT 절을 포함하고 대상에 일치하는 행이 없으며 원본이 중복 값을 포함하는 경우에는 대상이 원본에서 each 복사를 위해 해당 행의 복사본을 하나 가져옵니다. (아래에 한 예가 포함되어 있습니다.)
예¶
간단한 병합을 수행합니다.
테이블을 만들고 로딩합니다.
CREATE TABLE target_table (ID INTEGER, description VARCHAR); CREATE TABLE source_table (ID INTEGER, description VARCHAR);INSERT INTO target_table (ID, description) VALUES (10, 'To be updated (this is the old value)') ; INSERT INTO source_table (ID, description) VALUES (10, 'To be updated (this is the new value)') ;MERGE 문을 실행합니다.
MERGE INTO target_table USING source_table ON target_table.id = source_table.id WHEN MATCHED THEN UPDATE SET target_table.description = source_table.description; +------------------------+ | number of rows updated | |------------------------| | 1 | +------------------------+대상 테이블에 새 값을 표시합니다(원본 테이블은 변경되지 않음).
SELECT * FROM target_table; +----+---------------------------------------+ | ID | DESCRIPTION | |----+---------------------------------------| | 10 | To be updated (this is the new value) | +----+---------------------------------------+ SELECT * FROM source_table; +----+---------------------------------------+ | ID | DESCRIPTION | |----+---------------------------------------| | 10 | To be updated (this is the new value) | +----+---------------------------------------+
여러 작업(삭제, 업데이트, 삽입)이 혼합된 기본적인 병합을 수행합니다.
MERGE INTO t1 USING t2 ON t1.t1Key = t2.t2Key WHEN MATCHED AND t2.marked = 1 THEN DELETE WHEN MATCHED AND t2.isNewStatus = 1 THEN UPDATE SET val = t2.newVal, status = t2.newStatus WHEN MATCHED THEN UPDATE SET val = t2.newVal WHEN NOT MATCHED THEN INSERT (val, status) VALUES (t2.newVal, t2.newStatus);
원본에 중복 값이 있고 대상에는 일치하는 값이 없는 병합을 수행합니다. 원본 레코드의 모든 복사본이 대상에 삽입됩니다.
두 테이블을 모두 자르고 새 행을 원본 테이블에 로딩합니다. 행에 중복 항목이 포함되어 있습니다.
TRUNCATE TABLE source_table; TRUNCATE TABLE target_table; INSERT INTO source_table (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 문을 실행합니다.
MERGE INTO target_table USING source_table ON target_table.id = source_table.id WHEN MATCHED THEN UPDATE SET target_table.description = source_table.description WHEN NOT MATCHED THEN INSERT (ID, description) VALUES (source_table.id, source_table.description); +-------------------------+------------------------+ | number of rows inserted | number of rows updated | |-------------------------+------------------------| | 2 | 0 | +-------------------------+------------------------+대상 테이블에 새 값을 표시합니다.
SELECT ID FROM target_table; +----+ | ID | |----| | 50 | | 50 | +----+
비결정적 및 결정적 결과를 생성하는 조인을 사용해 레코드를 병합합니다.
-- Setup for example. CREATE TABLE target_orig (k NUMBER, v NUMBER); INSERT INTO target_orig VALUES (0, 10); CREATE TABLE src (k NUMBER, v NUMBER); INSERT INTO src VALUES (0, 11), (0, 12), (0, 13); -- Multiple updates conflict with each other. -- If ERROR_ON_NONDETERMINISTIC_MERGE=true, returns an error; -- otherwise updates target.v with a value (e.g. 11, 12, or 13) from one of the duplicate rows (row not defined). CREATE OR REPLACE TABLE target CLONE target_orig; MERGE INTO target USING src ON target.k = src.k WHEN MATCHED THEN UPDATE SET target.v = src.v; -- Updates and deletes conflict with each other. -- If ERROR_ON_NONDETERMINISTIC_MERGE=true, returns an error; -- otherwise either deletes the row or updates target.v with a value (e.g. 12 or 13) from one of the duplicate rows (row not defined). CREATE OR REPLACE TABLE target CLONE target_orig; MERGE INTO target USING src ON target.k = src.k WHEN MATCHED AND src.v = 11 THEN DELETE WHEN MATCHED THEN UPDATE SET target.v = src.v; -- Multiple deletes do not conflict with each other; -- joined values that do not match any clause do not prevent the delete (src.v = 13). -- Merge succeeds and the target row is deleted. CREATE OR REPLACE TABLE target CLONE target_orig; MERGE INTO target USING src ON target.k = src.k WHEN MATCHED AND src.v <= 12 THEN DELETE; -- Joined values that do not match any clause do not prevent an update (src.v = 12, 13). -- Merge succeeds and the target row is set to target.v = 11. CREATE OR REPLACE TABLE target CLONE target_orig; MERGE INTO target USING src ON target.k = src.k WHEN MATCHED AND src.v = 11 THEN UPDATE SET target.v = src.v; -- Use GROUP BY in the source clause to ensure that each target row joins against one row -- in the source: CREATE OR REPLACE TABLE target CLONE target_orig; 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);
다음 예에서 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) < -30) s ON m.id = s.id WHEN MATCHED THEN UPDATE SET m.fee = 40;