소스: sql-src/adv_08_merge/01_merge_basic.sql. 대상 emp_stat 은 사원별 누계(건수 cnt, 합계 total, 메모 note 는 기본값 '없음')이고, 원천 daily_in 은 오늘 들어온 값입니다. 원천에는 기존 키 1·3 과 새 키 6·7 이 있습니다.
ID | NAME | CNT | TOTAL | NOTE
---+--------+-----+-------+-----
1 | 김개발 | 10 | 1000 | 우수
2 | 남개발 | 8 | 800 | 보통
3 | 이영업 | 12 | 1500 | 우수
4 | 박영업 | 5 | 400 | 신입
(4행)ID | NAME | CNT | AMT
---+--------+-----+----
1 | 김개발 | 2 | 150
3 | 이영업 | 1 | 100
6 | 최신규 | 3 | 90
7 | 정신규 | 1 | 40
(4행)위가 대상 emp_stat, 아래가 원천 daily_in 입니다.
있는 키(1, 3)는 누계에 더하고, 없는 키(6, 7)는 새 행으로 넣습니다.
MERGE INTO emp_stat t
USING daily_in s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.cnt = t.cnt + s.cnt, t.total = t.total + s.amt
WHEN NOT MATCHED THEN
INSERT (id, name, cnt, total)
VALUES (s.id, s.name, s.cnt, s.amt);ID | NAME | CNT | TOTAL | NOTE
---+--------+-----+-------+-----
1 | 김개발 | 12 | 1150 | 우수
2 | 남개발 | 8 | 800 | 보통
3 | 이영업 | 13 | 1600 | 우수
4 | 박영업 | 5 | 400 | 신입
6 | 최신규 | 3 | 90 | 없음
7 | 정신규 | 1 | 40 | 없음
(6행)1 번은 1000+150, 3 번은 1500+100 이 됐고 원천에 없던 2·4 번은 그대로입니다. 새로 들어간 6·7 번은 INSERT 에 note 를 지정하지 않아 기본값 '없음' 이 들어갔습니다. 기존 행의 note('우수')는 UPDATE SET 에 없으므로 그대로입니다.
원천에 키 1 이 한 건 더 들어왔다고 합시다. 대상의 1 번 행을 두 번 고치려는 것이라 MERGE 는 오류를 냅니다. 이 문장만 한 줄로 씁니다.
INSERT INTO daily_in VALUES (1, '김개발', 4, 60);
MERGE INTO emp_stat t USING daily_in s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.cnt = t.cnt + s.cnt WHEN NOT MATCHED THEN INSERT (id, name, cnt, total) VALUES (s.id, s.name, s.cnt, s.amt);예상 오류: Unique index or primary key violation: "Merge using ON column expression, duplicate _ROWID_ target record already processed:_ROWID_=1:in:PUBLIC.EMP_STAT"Oracle 은 ORA-30926(원천 테이블에서 안정된 행 집합을 얻을 수 없음), MSSQL 도 같은 대상 행을 두 번 갱신할 수 없다는 오류를 냅니다. 오류가 나면 문장 전체가 취소되어 대상 표는 예제 2 결과 그대로입니다.
ID | NAME | CNT | TOTAL
---+--------+-----+------
1 | 김개발 | 12 | 1150
2 | 남개발 | 8 | 800
3 | 이영업 | 13 | 1600
4 | 박영업 | 5 | 400
6 | 최신규 | 3 | 90
7 | 정신규 | 1 | 40
(6행)주의위 오류 메시지는 H2 가 붙인 것이고 Oracle·MSSQL 의 문구와 다릅니다. 원천 중복이 오류가 된다는 점만 세 DB 가 같습니다.
원천 중복은 USING 자리에 GROUP BY 서브쿼리를 두어 키마다 한 행으로 만들면 해결됩니다. 위 오류 뒤 원천에는 키 1 이 두 건(150, 60)이 있는 상태입니다.
MERGE INTO emp_stat t
USING (
SELECT
id
, MAX(name) AS name
, SUM(cnt) AS cnt
, SUM(amt) AS amt
FROM daily_in
GROUP BY id
) s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.cnt = t.cnt + s.cnt, t.total = t.total + s.amt
WHEN NOT MATCHED THEN
INSERT (id, name, cnt, total)
VALUES (s.id, s.name, s.cnt, s.amt);ID | NAME | CNT | TOTAL
---+--------+-----+------
1 | 김개발 | 18 | 1360
2 | 남개발 | 8 | 800
3 | 이영업 | 14 | 1700
4 | 박영업 | 5 | 400
6 | 최신규 | 6 | 180
7 | 정신규 | 2 | 80
(6행)이 결과는 예제 2 를 이미 한 번 실행한 뒤 다시 실행한 것이라 값이 누적됐습니다(같은 데이터를 두 번 더한 것과 같음). 재실행하면 이중 반영되는 점은 5절에서 다룹니다.
ON (t.id = s.id) 에 쓴 id 를 UPDATE SET t.id = ... 로 바꾸려는 실수입니다. Oracle 은 ORA-38104(ON 절에 참조된 컬럼은 갱신할 수 없음)로 막습니다.
MERGE INTO emp_stat t
USING (SELECT id, id + 100 AS new_id FROM daily_in WHERE id = 3) s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.id = s.new_id;ID | NAME | CNT | TOTAL
----+--------+-----+------
1 | 김개발 | 18 | 1360
2 | 남개발 | 8 | 800
4 | 박영업 | 5 | 400
6 | 최신규 | 6 | 180
7 | 정신규 | 2 | 80
103 | 이영업 | 14 | 1700
(6행)H2 는 오류 없이 실행해 3 번 키를 103 으로 바꿨습니다. 이는 H2 에서 재현 안 됨, 문법 검토만 해당하는 사례로, 실제 Oracle 에서는 ORA-38104 로 실패합니다. 키를 바꿔야 하면 MERGE 가 아니라 별도 UPDATE 로 합니다.