[Oracle] 서브쿼리와 머지를 사용하여 컬럼 업데이트 하는 방법 (오라클 업데이트 예시)

오라클 업데이트 문 사용 시 자주 사용할 수 있는 서브쿼리, 머지 형태 예시자료입니다.

예시에서 TA 테이블이 업데이트 타깃이고, TB 테이블은 업데이트 시 참고하려는 소스 테이블입니다. 그리고 두 테이블 간의 조인할 수 있는 키는 총 7개(각각 컬럼 이름이 다음과 같습니다. E, C, V, D, O, L, U)로 가정합니다.

업데이트 레프트 조인 인라인 뷰

첫 번째 방법은 TA 테이블을 TB 테이블과 레프트 조인한 인라인 뷰에 대하여 업데이트를 시도합니다. 해당의 경우 키 보존이 이루어져야 합니다. 키 보존이 아닌 경우에는 다음의 오류가 발생합니다.

ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다

ORA-01779: cannot modify a column which maps to a non key-preserved table

SQL> UPDATE (SELECT *
  2            FROM    TA
  3                 LEFT JOIN
  4                    TB
  5                 ON     0 = 0
  6                    AND BE = AE
  7                    AND BC = AC
  8                    AND BV = AV
  9                    AND BD = AD
 10                    AND BO = AO
 11                    AND BL = AL
 12                    AND BU = AU)
 13     SET AI = BI;
   SET AI = BI
       *
13행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다

업데이트 서브쿼리 (상관 하위쿼리 : Correlated subquery)

두 번째 방법은 서브쿼리를 이용하는 방법입니다. 아마 가장 많이 사용하는 방법이 아닐까 합니다. WHERE 이하 부분을 생략하면 TA 테이블의 모든 행에 대해 실행하는 부분을 기억해 주세요.

SQL> UPDATE TA
  2     SET AI =
  3            (SELECT BI
  4               FROM TB
  5              WHERE     0 = 0
  6                    AND BE = AE
  7                    AND BC = AC
  8                    AND BV = AV
  9                    AND BD = AD
 10                    AND BO = AO
 11                    AND BL = AL
 12                    AND BU = AU)
 13   WHERE EXISTS
 14            (SELECT NULL
 15               FROM TB
 16              WHERE     0 = 0
 17                    AND BE = AE
 18                    AND BC = AC
 19                    AND BV = AV
 20                    AND BD = AD
 21                    AND BO = AO
 22                    AND BL = AL
 23                    AND BU = AU);
99 행이 갱신되었습니다.

머지 문으로 업데이트

세 번째 방법은 머지 문장으로 매치가 되는 경우에만 업데이트 하도록 합니다.

SQL> MERGE INTO TA
  2       USING TB
  3          ON (    0 = 0
  4              AND BE = AE
  5              AND BC = AC
  6              AND BV = AV
  7              AND BD = AD
  8              AND BO = AO
  9              AND BL = AL
 10              AND BU = AU)
 11  WHEN MATCHED
 12  THEN
 13     UPDATE SET AI = BI;
99 행이 병합되었습니다.