[Oracle/오라클] MERGE - 조건에 따른 UPDATE / INSERT

MERGE - 조건에 따라 갱신(UPDATE)하거나 입력(INSESRT)을 수행

---

문법설명 출처 - https://goddaehee.tistory.com/70 [갓대희의 작은공간]

MERGE INTO table_name alias         -- (실제 데이터를 INSERT 또는 UPDATE할 테이블)

USING (table | view | subquery) alias 

-- 실제 데이터를 조회할 대상 테이블(뷰, 서브쿼리)

-- 대상 테이블이 없는 경우 DUAL 테이블 사용

ON (join condition) 

-- 조건절 (Where절) 이 조건에 의해 아래 MATCHED / NOT MATCHED 로 분기

WHEN MATCHED THEN  -- ON 조건에 해당하는 데이터(레코드)가 존재한다면 

UPDATE SET [column1] = [value1] -- 해당 레코드를 대상으로 UPDATE 실행

WHEN NOT MATCHED THEN -- ON 이하의 조건에 해당하는 데이터(레코드)가 존재하지 않는다면

INSERT (column1, column2 ...) VALUES (value1, value2 ...); 

-- 새 데이터를 추가해야 하므로 INSERT 실행

 

실행시 오류의 발생 원인과 해결방법

ORA-30926 : 원본 테이블의 고정 행 집합을 가져올 수 없습니다.

ORA-30926 : unable to get a stable set of rows in the source tables

 

MERGE INTO 구문 중 INSERT와 UPDATE는 하나의 레코드를 대상으로 작업을 수행할 수 있는데, 2개 이상의 SELECT 결과가 나와서 발생하는 오류였다.

 

USING ( ) ON ( ) 조건에 의해 SELECT 된 결과가 2건이상 발생하는 경우 동일한 레코드가 중복 삽입되는 될 수 있으며, 이때 PK 무결성 오류가 발생하기도 한다.

또는 update되는 로우가 1건 이상이 발생한다.

 

1) INTO 절에 사용되는 테이블에 Primary Key 를 사용하는 경우    

 - 즉 INSERT 구문에서 DUPLICATE가 발생하거나 UPDATE 에 MULTI ROW가 UPDATE되는 경우

2) ON 구문에서 UPDATE되는 ROW가 1개 이상일 경우

 - 즉 ON 구문에서 맞는 테이블 값이  하나 이상일 경우

그렇기 때문에 USING ( ) 조건에 의해 산출되는 SELECT 결과가 중복 레코드를 갖지 않도록 수정하면 해결된다.

---

오류발생 예시

MERGE INTO emp_mast t
USING emp e
ON (t.deptno = e.deptno) --중복되는 deptno 값으로 실행하는 경우 
WHEN MATCHED THEN
UPDATE SET t.sal = t.sal * 2,
t.total = t.sal * 2 + t.comm
WHEN NOT MATCHED THEN
INSERT (t.EMPNO, t.ENAME, t.JOB, t.MGR, t.HIREDATE, t.SAL, t.COMM, t.DEPTNO)
VALUES (e.EMPNO, e.ENAME, e.JOB, e.MGR, e.HIREDATE, e.SAL, e.COMM, e.DEPTNO);

ORA-30926: unable to get a stable set of rows in the source tables

PK 값 empno 조건으로 실행

select * from emp_mast;

MERGE INTO emp_mast t
USING emp e
ON (t.empno = e.empno)
WHEN MATCHED THEN
UPDATE SET t.sal = t.sal * 2,
t.total = t.sal * 2 + t.comm
WHEN NOT MATCHED THEN
INSERT (t.EMPNO, t.ENAME, t.JOB, t.MGR, t.HIREDATE, t.SAL, t.COMM, t.DEPTNO)
VALUES (e.EMPNO, e.ENAME, e.JOB, e.MGR, e.HIREDATE, e.SAL, e.COMM, e.DEPTNO);