[ORACLE] 다중 테이블을 JOIN 하여 UPDATE 하기

오라클은 다중 테이블을 조인하여 데이터를 갱신하는 기능을 지원합니다.

T1 테이블 A1, A2, A3 컬럼, T2 테이블 B1, B2, B3 컬럼에서 A2 와 B2 의 데이터가 같고 A1 의 데이터가 1 인경우 A3 을 B3 으로 업데이트 한다고 합니다.

CREATE TABLE A ( A1 INT, A2 INT, A3 INT );
CREATE TABLE B ( B1 INT, B2 INT, B3 INT );
INSERT INTO A VALUES (1, 3, 3);
INSERT INTO B VALUES (1, 3, 5);
COMMIT;

UPDATE 문은 다음과 같이 작성할 수 있습니다.

UPDATE A SET A3 = ( SELECT B3
                    FROM B
                    WHERE A.A2 = B.B2 AND
		    A.A1 = 1 );

이후 A 테이블의 데이터를 조회하면 A3 컬럼이 B3 의 데이터가 되는 것을 확인할 수 있습니다.

SQL> SELECT * FROM A;

        A1         A2         A3
---------- ---------- ----------
         1          3          5

그런데 만약 일치하는 데이터가 없다고 하면 어떻게 될까요? 기존 데이터가 없기 때문에 NULL 값으로 업데이트 됩니다.

A.A1 을 2 로 설정하고 업데이트 한 경우

SQL> SELECT * FROM A;

        A1         A2         A3
---------- ---------- ----------
         1          3

만약 NULL 인경우 업데이트를 원치 않는 경우에는 NVL 함수를 써야 합니다.

이 구문을 UPDATE JOIN 을 사용하여 업데이트를 해보겠습니다.

먼저, JOIN 대상이 되는 테이블의 부모/자식 관계를 오라클이 알고있어야 합니다. 이를 알지 못하는 경우 다음과 같은 에러가 발생합니다.

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

위 테이블에서 부모와 자식관계를 알려주기 위해 B 테이블에 제약조건을 추가합니다.

SQL> ALTER TABLE B ADD CONSTRAINT B_PK PRIMARY KEY (B2);

이후 UPDATE JOIN 을 사용하여 데이터를 갱신합니다.

SQL> UPDATE ( SELECT A.A3 A_A3, B.B3 B_B3
              FROM A, B
              WHERE A.A2 = B.B2 AND
                    A.A1 = 1 ) SET A_A3 = B_B3;

1 row updated.

SQL> SELECT * FROM A;

        A1         A2         A3
---------- ---------- ----------
         1          3          5

오라클 11g 이전버전에는 /*+ BYPASS_UVJC */ 힌트를 사용하여 위의 에러를 무시했지만, 이는 적절치 못하는 방법이며 부모/자식의 관계를 오라클이 알도록 하는게 가장 좋은 방법입니다.