[Oracle] 오라클 UPDATE 방법 & 노하우 정리 (데이터 수정)

오라클에서 테이블의 값을 수정하기 위해서는 UPDATE 문을 사용하면 된다. WHERE 절에 해당하는 행이 여러 개인 경우 한 번에 여러 건의 행이 SET 절의 값으로 변경된다. UPDATE 문은 시스템 운영 시 자주 사용하므로 다양한 방법을 익혀두는 것이 좋다.

 

오라클 UPDATE 문

기본 사용법

UPDATE emp
   SET job = 'MANAGER'
     , deptno = 20
 WHERE empno = 7566

 

위는 WHERE 절의 empno에 해당하는 데이터의 job, deptno 컬럼을 수정하는 쿼리이다.  empno는 PK이기 때문에 한건의 데이터만 수정되지만 PK가 아니고 여러 건이 조회되면 여러 건의 데이터가 한 번에 수정된다.

 

SET 절에 서브쿼리 사용법

UPDATE emp a
   SET a.sal  = (SELECT MAX(aa.sal) FROM emp aa WHERE aa.job = a.job)
     , a.comm = (SELECT MAX(aa.comm) FROM emp aa WHERE aa.job = a.job)
 WHERE a.job = 'ANALYST'

 

다른 테이블에서 데이터를 조회하여 UPDATE를 해야 하는 경우가 있다. 조인을 사용하면 편리할 거 같지만 UPDATE문에서 조인은 제약사항이 많기 때문에 서브 쿼리를 활용하면 쉽게 해결되는 경우가 많다.

 

UPDATE emp a
   SET (a.sal, a.comm)  = (SELECT MAX(aa.sal), MAX(aa.comm)
                             FROM emp aa 
                            WHERE aa.job = a.job)
 WHERE a.job = 'ANALYST'

 

동일한 서브쿼리를 반복해서 사용해야 하는 경우 컬럼을 묶어서 한 번의 서브 쿼리로 업데이트 값을 입력할 수 있다.

 

WHERE 절에 IN 사용법

UPDATE emp
   SET sal = 3000
 WHERE empno IN (SELECT aa.empno
                   FROM emp aa
                      , dept bb
                  WHERE aa.job = 'ANALYST'
                    and aa.deptno = bb.deptno)

 

업데이트 범위를 정할 때 다른 테이블을 참조해야 할 경우 IN을 사용하여 데이터를 포함시키거나 NOT IN을 사용하여 제외시킬 수 있다.

 

UPDATE emp
   SET comm = 500
 WHERE (job, deptno) IN (SELECT aa.job, aa.deptno
                           FROM emp aa
                          WHERE aa.deptno = 30)

 

IN의 서브 쿼리에서 키값이 여러 개인 경우 컬럼을 묶어서 조건을 맵핑할 수 있다.

 

UPDATE emp a
   SET a.comm = 500
 WHERE a.ROWID IN (SELECT aa.ROWID
                     FROM emp aa
                    WHERE aa.deptno = 30)

 

동일한 데이블을 사용한다면 ROWID를 키로 사용할 수 있다.

 

WHERE 절에 EXISTS 사용법

UPDATE emp a
   SET a.comm = 500
 WHERE EXISTS (SELECT 1
                 FROM emp aa
                WHERE aa.empno = a.empno
                  AND aa.deptno = 30)

 

EXISTS를 사용하면 쉽게 데이터를 제외시키거나 포함시킬 수 있다. IN과 비슷한 역할을 하지만 성능면에서 조금 더 나을 수 있다.

 

UPDATE emp a
   SET a.comm = 500
 WHERE NOT EXISTS (SELECT 1
                     FROM emp aa
                    WHERE aa.empno = a.empno
                      AND aa.deptno = 30)

 

조인하여 UPDATE 하는 방법

UPDATE (SELECT a.sal
             , a.comm
             , b.sal AS sal_2
             , b.comm AS comm_2
          FROM emp a
             , emp_man b
         WHERE a.empno = b.empno)
   SET sal = sal_2 
     , comm = comm_2

 

두 개의 테이블을 조인해서 업데이트해야 할 경우 사용하는 방법이다. 위의 쿼리는 오라클 11.2 이하의 버전에서는 오류가 발생하며 아래의 MERGE 문을 사용해야 한다.

 

MERGE 
 INTO emp a
USING emp_man b
   ON (a.empno = b.empno)
 WHEN MATCHED THEN
      UPDATE 
         SET a.sal = b.sal
           , a.comm = b.comm;

 

MERGE 문은 오라클 9i부터 사용할 수 있다. BYPASS_UJVC 힌트를 사용하는 방법이 있으나 11g부터 공식적으로 사용을 중단하였기 때문에 특별한 경우가 아니면 MERGE 문을 사용할 것을 권장한다.

[Oracle] 오라클 MERGE INTO 사용법 및 노하우 정리

[Oracle] 오라클 실수로 지운 데이터 복구 쿼리 (TIMESTAMP)