소스: sql-src/adv_08_merge/02_mysql_upsert.sql. SET MODE MySQL 로 같은 데이터(대상 4행, 원천 4행)에 세 문법을 각각 실행합니다. 먼저 ON DUPLICATE KEY UPDATE 입니다. 건수와 합계를 더합니다.
INSERT INTO emp_stat (id, name, cnt, total)
SELECT id, name, cnt, amt FROM daily_in
ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt), total = total + VALUES(total);ID | NAME | CNT | TOTAL | NOTE
---+--------+-----+-------+-----
1 | 김개발 | 12 | 1150 | 우수
2 | 남개발 | 8 | 800 | 보통
3 | 이영업 | 13 | 1600 | 우수
4 | 박영업 | 5 | 400 | 신입
6 | 최신규 | 3 | 90 | 없음
7 | 정신규 | 1 | 40 | 없음
(6행)결과는 MERGE 예제 2 와 같습니다. VALUES(col) 은 "INSERT 하려던 값" 을 가리키는데, MySQL 8.0.20 부터 폐기 예정이라 8.0.19 부터 쓸 수 있는 별칭 형식을 권장합니다. 이 형식은 H2 에서 실행하지 않고 문법만 봅니다.
-- MySQL 8.0.19 이상 (H2 에서 실행하지 않음)
INSERT INTO emp_stat (id, name, cnt, total)
VALUES (1, '김개발', 2, 150)
AS new
ON DUPLICATE KEY UPDATE
cnt = emp_stat.cnt + new.cnt
, total = emp_stat.total + new.total;다음은 INSERT IGNORE 입니다. 충돌한 1·3 번은 건너뛰고 6·7 번만 들어갑니다(초기 상태로 되돌린 뒤 실행).
INSERT IGNORE INTO emp_stat (id, name, cnt, total)
SELECT id, name, cnt, amt FROM daily_in;ID | NAME | CNT | TOTAL | NOTE
---+--------+-----+-------+-----
1 | 김개발 | 10 | 1000 | 우수
2 | 남개발 | 8 | 800 | 보통
3 | 이영업 | 12 | 1500 | 우수
4 | 박영업 | 5 | 400 | 신입
6 | 최신규 | 3 | 90 | 없음
7 | 정신규 | 1 | 40 | 없음
(6행)1·3 번의 값은 오늘 값이 반영되지 않고 그대로입니다. INSERT IGNORE 는 키 충돌 외에 다른 오류(형 변환 실패, 길이 초과 등)도 경고로 바꿔 넘기므로 잘못된 데이터가 조용히 들어가거나 빠질 수 있습니다.
마지막은 REPLACE INTO 입니다. 1 번 행 하나를 note 없이 넣습니다.
REPLACE INTO emp_stat (id, name, cnt, total) VALUES (1, '김개발', 12, 1150);ID | NAME | CNT | TOTAL | NOTE
---+--------+-----+-------+-----
1 | 김개발 | 12 | 1150 | 우수
2 | 남개발 | 8 | 800 | 보통
3 | 이영업 | 12 | 1500 | 우수
4 | 박영업 | 5 | 400 | 신입
(4행)주의이 결과는 H2 에서 재현 안 됨입니다. H2 는
REPLACE를 기존 행을 고치는 것처럼 처리해 1 번의note가 '우수' 로 남았지만, 실제 MySQL 은 충돌한 행을 DELETE 한 뒤 INSERT 하므로note가 기본값 '없음' 으로 바뀝니다. 지정하지 않은 컬럼이 기본값이나 NULL 이 되고 이 점이ON DUPLICATE KEY UPDATE와 결정적으로 다릅니다.
Oracle·Tibero 는 UPDATE SET 뒤에 WHERE 로 조건을 걸고, 이어서 DELETE WHERE 로 방금 갱신한 행 중 지울 것을 고릅니다. 이 문법은 H2 미지원이라 문법 검토만 하고, 결과는 예제 데이터로 그린 도식입니다. 오늘 값이 100 이상일 때만 누계에 더하고, 더한 결과 합계가 1600 이상이면 그 행을 지웁니다.
-- Oracle · Tibero (H2 미지원, 문법 검토만)
MERGE INTO emp_stat t
USING daily_in s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.total = t.total + s.amt
WHERE s.amt >= 100
DELETE WHERE t.total >= 1600
WHEN NOT MATCHED THEN
INSERT (id, name, cnt, total)
VALUES (s.id, s.name, s.cnt, s.amt);도식(H2 미지원, 실행 결과 아님)
ID | NAME | CNT | TOTAL
---+--------+-----+------
1 | 김개발 | 10 | 1150
2 | 남개발 | 8 | 800
4 | 박영업 | 5 | 400
6 | 최신규 | 3 | 90
7 | 정신규 | 1 | 403 번은 원천 값이 100 이라 갱신되어 1600 이 됐고, DELETE WHERE 가 갱신된 값(1600)을 기준으로 참이라 지워졌습니다. DELETE 는 이번 MERGE 가 갱신한 행에만 적용되므로 갱신하지 않은 2·4 번은 조건과 무관하게 남습니다.
MSSQL 과 H2 표준형에는 이 문법이 없고, 대신 WHEN MATCHED AND 조건 THEN UPDATE, WHEN MATCHED AND 조건 THEN DELETE 처럼 WHEN 절을 나눕니다. 소스 03_dialects.sql 에서 표준형을 실행했습니다. 초기 대상에 6·7 번을 넣은 상태에서 오늘 값 100 이상이면 더하고 100 미만이면 지웁니다.
MERGE INTO emp_stat t
USING daily_in s
ON (t.id = s.id)
WHEN MATCHED AND s.amt >= 100 THEN
UPDATE SET t.total = t.total + s.amt
WHEN MATCHED AND s.amt < 100 THEN
DELETE;ID | NAME | CNT | TOTAL
---+--------+-----+------
1 | 김개발 | 10 | 1150
2 | 남개발 | 8 | 800
3 | 이영업 | 12 | 1600
4 | 박영업 | 5 | 400
(4행)6·7 번(오늘 값 90, 40)이 삭제되고 1·3 번이 갱신됐습니다. 이 앞의 Oracle 모드 실행에서는 WHEN NOT MATCHED 만 쓰는 MERGE 로 6·7 번을 먼저 넣어 두었습니다.
MSSQL MERGE 는 세 가지가 더 있습니다. 세미콜론으로 끝내야 하고, 대상에만 있는 행을 WHEN NOT MATCHED BY SOURCE 로 다루며, OUTPUT $action 으로 각 행에 무엇을 했는지 돌려받습니다. BY SOURCE 와 OUTPUT 은 H2 미지원이라 문법 검토만 합니다.
-- MSSQL (H2 미지원, 문법 검토만)
MERGE INTO emp_stat WITH (HOLDLOCK) AS t
USING daily_in AS 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)
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET t.note = '미입력'
OUTPUT $action AS act, COALESCE(inserted.id, deleted.id) AS id;도식(H2 미지원, 실행 결과 아님)
ACT | ID
-------+---
UPDATE | 1
UPDATE | 2
UPDATE | 3
UPDATE | 4
INSERT | 6
INSERT | 7ACT 가 UPDATE 인 행 중 2·4 번은 BY SOURCE 절이 처리한 것이고, 출력 순서는 보장되지 않습니다. MSSQL MERGE 는 여러 세션이 동시에 같은 새 키를 넣으면 중복 키 오류가 날 수 있어서 WITH (HOLDLOCK) 힌트를 함께 쓰라는 권고가 있습니다.
같은 문장에서 BY SOURCE 절과 OUTPUT 을 뺀 기본형은 H2 의 SET MODE MSSQLServer 에서 실행됩니다. WHEN MATCHED 에서 건수만 더하는 형태로 실행했습니다.
MERGE 를 쓸 수 없거나 쓰고 싶지 않을 때는 먼저 UPDATE 하고, 대상에 없는 키만 INSERT 합니다. 어느 DB 에서나 되지만 대상을 두 번 읽고, 두 문장 사이에 다른 세션이 같은 키를 넣을 수 있습니다.
UPDATE emp_stat
SET cnt = cnt + (SELECT SUM(s.cnt) FROM daily_in s WHERE s.id = emp_stat.id)
, total = total + (SELECT SUM(s.amt) FROM daily_in s WHERE s.id = emp_stat.id)
WHERE id IN (SELECT id FROM daily_in);
INSERT INTO emp_stat (id, name, cnt, total)
SELECT
s.id
, s.name
, s.cnt
, s.amt
FROM daily_in s
WHERE NOT EXISTS (SELECT 1 FROM emp_stat t WHERE t.id = s.id);ID | NAME | CNT | TOTAL
---+--------+-----+------
1 | 김개발 | 12 | 1150
2 | 남개발 | 8 | 800
3 | 이영업 | 13 | 1600
4 | 박영업 | 5 | 400
6 | 최신규 | 3 | 90
7 | 정신규 | 1 | 40
(6행)SUM 서브쿼리를 쓰므로 원천에 같은 키가 여러 건이어도 오류 없이 합쳐서 더합니다. 이 결과는 초기 대상 4행에 원천 4행을 반영한 것입니다.
팁대량 처리에서는 UPDATE 후 0건이면 INSERT 하는 방식이 두 번 접근하고 동시성 문제가 있으므로
MERGE(MySQL 은ON DUPLICATE KEY UPDATE)가 낫습니다. 다만 원천 중복이 없다는 것을 먼저 확인해야 합니다.