오라클 다중 테이블 UPDATE - olakeul dajung teibeul 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 */ 힌트를 사용하여 위의 에러를 무시했지만, 이는 적절치 못하는 방법이며 부모/자식의 관계를 오라클이 알도록 하는게 가장 좋은 방법입니다.

Oracle, MySql, MsSql 2개이상 복수 테이블 update 하는 방법

한번에 복수의 테이블에 값을 업데이트 하는 방법은 

Oracle, MySql, MsSql 에서 모두 같은 방식으로 가능합니다.

Inner Join(Right Join)으로 데이터를 가져와 업데이트 하면됩니다. 

Inner Join 에 대한 설명은 아래의 링크를 참고하세요.

aljjabaegi.tistory.com/13

INNER JOIN, LEFT JOIN(OUTER JOIN) 비교 이너조인, 아우터조인

* INNER JOIN 과 OUTER JOIN(LEFT, RIGHT) 의 비교 이너조인, 아우터조인 [JOIN사용법] SELECT * FROM 테이블1 T1 LEFT JOIN 테이블2 T2 ON (T2.테이블을 연결할 컬럼 = T1.테이블을 연결할 컬럼 ); INNER는 생략..

aljjabaegi.tistory.com

예를들어 A 테이블의 a값과 B테이블의 a 값이 키로 연결성을 갖는다고 할 때,

A테이블의 c 값과 B테이블의 d 값을 수정하는 쿼리를 작성해보겠습니다.

UPDATE A T1
 RIGHT JOIN B T2 ON (T2.a = T1.a)
   SET T1.c = '값1',
       T2.d = '값2'
 WHERE A.e = '조건 값'

Outer Join 으로 동작하지 않는 이유는 위의 Link를 보시면 이해가 되실꺼에요.

여러개 테이블의 값을 수정할 때도 Right Join을 활용하여 가져와 수정하시면 됩니다.

오라클 다중 업데이트 입니다.

두 테이블을 조인해서 업데이트를 하는 방법에는 두가지가 있는데 예제를 통해서 확인해 봅시다.

1. 일단 테이블을 생성합니다.

2.  적당한 데이터를 넣고

3. 첫번째 멀티 조인 업데이트 입니다. 업데이트 절에서 조인하고 set 절에서 조인한 컬럼 끼리 매칭 시켜주면 됩니다.

* 이 때 ORA-01779 키보존 오류가 날 수 있는데, b.사람이름=pk 를 만족해야 합니다. 만약 multi pk 라 하더라도 모두 명시해주시면 됩니다.

4. 두번째 멀티 조인 업데이트 입니다. 보통의 업데이트 구문처럼 사용하면서 SET 절과 WHERE 절에 각각 서브쿼리를 통해 조인을 합니다.

5. 결과는 이렇습니다.

여기서 실행 및 테스트 해볼 수 있습니다.

http://sqlfiddle.com/#!4/6432f/4