소스: sql-src/adv_10_multi_dml/02_merge_alt.sql. 고급 08 MERGE 에서 WHEN MATCHED 만 쓰면 조인 UPDATE 가 됩니다. 짝 없는 행은 ON 에 걸리지 않아 WHERE EXISTS 없이도 안전합니다. 아래는 예제 1 의 초기 데이터에서 실행한 결과입니다.
MERGE INTO emp t
USING raise_rate r
ON (t.dept = r.dept)
WHEN MATCHED THEN
UPDATE SET t.sal = t.sal + t.sal * r.pct / 100, t.bonus = r.bonus_amt;ID | NAME | DEPT | SAL | BONUS
---+--------+------+-----+------
1 | 김대표 | 경영 | 900 | 0
2 | 김개발 | 개발 | 550 | 50
3 | 남개발 | 개발 | 495 | 50
4 | 이영업 | 영업 | 420 | 20
5 | 박영업 | 영업 | 399 | 20
6 | 최인사 | 인사 | 350 | 0
(6행)예제 4 와 같은 결과입니다. MERGE 는 Oracle 9i·Tibero·MSSQL 2008 이 지원하고 MySQL 은 없으므로 MySQL 은 변형 3 의 UPDATE ... JOIN 을 씁니다.
raise_rate 에 영업이 두 행(5% 와 8%)이 되면 대상 한 행에 원천 두 행이 짝지어집니다. 상관 서브쿼리 UPDATE 는 서브쿼리가 두 행을 돌려줘서 오류입니다. 이 문장만 한 줄로 씁니다.
INSERT INTO raise_rate VALUES ('영업', 8, 25);
UPDATE emp SET bonus = (SELECT r.bonus_amt FROM raise_rate r WHERE r.dept = emp.dept) WHERE EXISTS (SELECT 1 FROM raise_rate r WHERE r.dept = emp.dept);예상 오류: Scalar subquery contains more than one row같은 상황에서 MERGE 도 오류가 납니다.
MERGE INTO emp t USING raise_rate r ON (t.dept = r.dept) WHEN MATCHED THEN UPDATE SET t.bonus = r.bonus_amt;예상 오류: Unique index or primary key violation: "Merge using ON column expression, duplicate _ROWID_ target record already processed:_ROWID_=4:in:PUBLIC.EMP"두 오류 뒤 emp 는 변하지 않았습니다. 오류 문구는 H2 것이고 Oracle 은 서브쿼리가 ORA-01427, MERGE 가 ORA-30926 입니다.
주의MSSQL
UPDATE ... FROM은 이 상황에서 오류를 내지 않습니다. 조인 결과 한 대상 행에 원천 행이 여러 개면 그중 하나가 임의로 적용되고, 어느 것인지 보장이 없습니다.MERGE는 오류를 내므로 원천이 유일하다고 확신할 수 없으면MERGE나 미리GROUP BY로 한 행으로 만든 원천을 씁니다.
이 문법은 H2 에서 미지원(UPDATE ... JOIN, UPDATE ... FROM)이라 sql 파일에 넣지 않았고 문법 검토만 합니다. 결과는 예제 1 데이터로 그린 도식이고 예제 4 와 같습니다. MySQL 은 UPDATE 뒤에 조인을 씁니다.
-- MySQL (H2 미지원, 문법 검토만)
UPDATE emp e
JOIN raise_rate r ON r.dept = e.dept
SET e.sal = e.sal + e.sal * r.pct / 100
, e.bonus = r.bonus_amt;MSSQL 은 SET 뒤에 FROM 절을 두고 조인합니다. 갱신 대상 별칭을 UPDATE 뒤에 씁니다.
-- MSSQL (H2 미지원, 문법 검토만)
UPDATE e
SET e.sal = e.sal + e.sal * r.pct / 100
, e.bonus = r.bonus_amt
FROM emp e
JOIN raise_rate r ON r.dept = e.dept;도식(H2 미지원, 실행 결과 아님)
ID | NAME | DEPT | SAL | BONUS
---+--------+------+-----+------
1 | 김대표 | 경영 | 900 | 0
2 | 김개발 | 개발 | 550 | 50
3 | 남개발 | 개발 | 495 | 50
4 | 이영업 | 영업 | 420 | 20
5 | 박영업 | 영업 | 399 | 20
6 | 최인사 | 인사 | 350 | 0내부 조인이라 짝 없는 경영·인사 행은 처리 대상이 아닙니다. 그래서 WHERE EXISTS 없이도 NULL 사고가 나지 않는 것이 상관 서브쿼리 방식과의 큰 차이입니다. 대신 원천이 중복이면 위 경고처럼 조용히 임의의 값이 들어갈 수 있습니다.
MySQL·MSSQL 은 DELETE 다음에 지울 표의 별칭을 쓰고 FROM 에서 조인합니다. 두 DB 의 모양이 같습니다.
-- MySQL · MSSQL (H2 미지원, 문법 검토만)
DELETE e
FROM emp e
JOIN resign r ON r.emp_id = e.id;Oracle 은 23ai 이전에는 WHERE EXISTS 를 씁니다(예제 5). 결과는 예제 5 의 첫 결과 표와 같아서 도식은 생략합니다.
조건마다 다른 표에 넣습니다. 급여 450 이상은 high_emp, 개발 부서는 dev_emp 로 나눕니다. ALL 이므로 개발이면서 450 이상인 사원은 두 표에 모두 들어갑니다.
-- Oracle · Tibero (H2 미지원, 문법 검토만)
INSERT ALL
WHEN sal >= 450 THEN INTO high_emp (id, name, sal) VALUES (id, name, sal)
WHEN dept = '개발' THEN INTO dev_emp (id, name, sal) VALUES (id, name, sal)
SELECT id, name, sal, dept FROM emp;FIRST 는 처음 맞는 WHEN 에만 넣고 ELSE 로 나머지를 받습니다. 급여 등급을 나누는 데 씁니다.
-- Oracle · Tibero (H2 미지원, 문법 검토만)
INSERT FIRST
WHEN sal >= 800 THEN INTO tier_a (id, name, sal) VALUES (id, name, sal)
WHEN sal >= 450 THEN INTO tier_b (id, name, sal) VALUES (id, name, sal)
ELSE INTO tier_c (id, name, sal) VALUES (id, name, sal)
SELECT id, name, sal FROM emp;두 문장의 결과는 아래 대체 쿼리 실행 결과와 같은 표가 됩니다. H2 에서는 WHEN 조건마다 INSERT ... SELECT 를 씁니다. 소스는 03_dialects.sql 이고 ALL 의 대체부터 봅니다.
INSERT INTO high_emp (id, name, sal)
SELECT id, name, sal FROM emp WHERE sal >= 450;
INSERT INTO dev_emp (id, name, sal)
SELECT id, name, sal FROM emp WHERE dept = '개발';ID | NAME | SAL
---+--------+----
1 | 김대표 | 900
2 | 김개발 | 500
3 | 남개발 | 450
(3행)ID | NAME | SAL
---+--------+----
2 | 김개발 | 500
3 | 남개발 | 450
(2행)위가 high_emp, 아래가 dev_emp 입니다. 김개발과 남개발이 두 표에 모두 들어갔습니다. FIRST 대체는 조건이 겹치지 않게 범위를 나눕니다.
INSERT INTO tier_a (id, name, sal)
SELECT id, name, sal FROM emp WHERE sal >= 800;
INSERT INTO tier_b (id, name, sal)
SELECT id, name, sal FROM emp WHERE sal >= 450 AND sal < 800;
INSERT INTO tier_c (id, name, sal)
SELECT id, name, sal FROM emp WHERE sal < 450;ID | NAME | SAL
---+--------+----
1 | 김대표 | 900
(1행)ID | NAME | SAL
---+--------+----
2 | 김개발 | 500
3 | 남개발 | 450
(2행)ID | NAME | SAL
---+--------+----
4 | 이영업 | 400
5 | 박영업 | 380
6 | 최인사 | 350
(3행)위에서부터 tier_a, tier_b, tier_c 입니다. INSERT ALL 은 원천을 한 번 읽지만 대체 방식은 조건 수만큼 읽습니다. MSSQL 은 OUTPUT ... INTO 로 한 문장의 결과를 다른 표에 받을 수 있다는 점만 언급합니다.
팁원천이 큰 표이면 조건별
INSERT ... SELECT를 같은 트랜잭션에 묶고, 조건이 서로 겹치는지 먼저 확인합니다.FIRST의 "처음 맞는 것만" 을 흉내 내려면 뒤 조건에 앞 조건의 부정을 넣어야 합니다.