[Oracle] merge into query

▶ 단일 테이블

USING 절에 테이블 대신 dual을 사용

MERGE 
 INTO emp a
USING dual
   ON (a.empno = 7788)
 WHEN MATCHED THEN
      UPDATE
         SET a.deptno = 20
 WHEN NOT MATCHED THEN
      INSERT (a.empno, a.ename, a.deptno)
      VALUES (7788, 'SCOTT', 20);

 

 

 

▶ 조인 테이블

MERGE 
 INTO depart a
USING emp b
   ON (a.empno = 7788 AND a.empno = b.empno)
 WHEN MATCHED THEN
      UPDATE
         SET a.job = b.job
           , a.deptno = b.deptno
 WHEN NOT MATCHED THEN
      INSERT (a.empno, a.job, a.deptno)
      VALUES (b.empno, b.job, b.deptno);

 

 

 

▶ 서브쿼리

MERGE 
 INTO emp a
USING (SELECT aa.empno
            , aa.job
            , aa.deptno
         FROM emp aa
            , dept bb
        WHERE aa.empno = 7788
          AND aa.deptno = bb.deptno) b
   ON (a.empno = b.empno)
 WHEN MATCHED THEN
      UPDATE
         SET a.job = b.job
           , a.deptno = b.deptno
 WHEN NOT MATCHED THEN
      INSERT (a.empno, a.job, a.deptno)
      VALUES (b.empno, b.job, b.deptno);

 

 

 

▶ DELETE 사용

WHERE 절을 사용하지 않고 DELETE 문만 작성하면 MATCHED 된 모든 데이터는 삭제된다.

MERGE 
 INTO emp a
USING dual
   ON (a.empno = 7788)
 WHEN MATCHED THEN
      UPDATE
         SET a.deptno = 20
       WHERE a.job = 'ANALYST'
      DELETE
       WHERE a.job <> 'ANALYST';

 

 

주의사항

ON 조건절에 사용할 컬럼을 업데이트하면 오류가 발생한다.