Join Update

Oracle에서 두 테이블을 Join하여 Update 쿼리는,

UPDATE AA

   SET c = ( SELECT c

               FROM BB

              WHERE AA.a = 1

                AND AA.b = 10

                AND BB.a = AA.a

                AND BB.b < AA.b )

 WHERE AA.a = 1

   AND AA.b = 10;

SET 절에 쓰이는 서브 쿼리와는 상관없이 WHERE 절에 해당하는 AA.a = 1 AND AA.b = 10 인 자료는 모두 업데이트 하는 것입니다.

업데이트의 기준은 WHERE 절로 SET 절의 서브 쿼리에 맞는 데이터가 없으면 Null 값으로 업데이트 한다.

SET 절의 서브 쿼리에서 AA 테이블과 관련 된 조건은 불필요하다. 이미 메인 쿼리의 WHERE 절에서 제한을 하고 있기 때문이다. SET절의 서브 쿼리에서 C 컬럼에 대한 정의가 불분명하다.

위의 쿼리를 보완 하면 아래와 같다.

UPDATE AA

   SET c = ( SELECT BB.c

               FROM BB

              WHERE BB.a = AA.a

                AND BB.b < AA.b )

 WHERE AA.a = 1

   AND AA.b = 10;

1. SET 절의 서브 쿼리 조건에 따라 Null 값 처리

UPDATE AA

   SET c = NVL( ( SELECT BB.c

                    FROM BB

                   WHERE BB.a = AA.a

                     AND BB.b < AA.b ), AA.c )

 WHERE AA.a = 1

   AND AA.b = 10;

다른 방법으로는 Group 함수를 취한 후에 NVL 함수를 사용하는 방법도 있다.

UPDATE AA

   SET c = ( SELECT NVL(MAX(BB.c), AA.c)

               FROM BB

              WHERE BB.a = AA.a

                AND BB.b < AA.b )

 WHERE AA.a = 1

   AND AA.b = 10;

위의 쿼리로 처리된 데이터는 이상이 없겠지만, 서브 쿼리에 의해서 조인 조건에 해당하지 않는 값에 대해 불필요한 업데이트가 발생한다.

2. EXISTS 체크 조건

SET 절과 같은 방법으로 체크 조건을 주어 WHERE 절의 해당 데이터를 한정 한다.

UPDATE AA

   SET c = ( SELECT BB.c

               FROM BB

              WHERE BB.a = AA.a

                AND BB.b < AA.b )

 WHERE AA.a = 1

   AND AA.b = 10

   AND EXISTS ( SELECT 'X'

                  FROM BB

                 WHERE BB.a = AA.a

                   AND BB.b < AA.b );

단점은, BB 테이블을 중복하여 엑세스하는 것이다.

3. 수정 가능 조인 뷰

UPDATE ( SELECT AA.c as AA_C

              , BB.c as BB_C

           FROM AA, BB

          WHERE AA.a = 1

            AND AA.b = 10

            AND BB.a = AA.a

            AND BB.b < AA.b )

   SET AA_C = BB_C;

효율적으로 데이터를 업데이트 하는 방법이다. 불필요한 업데이트가 발생하지 않으며, 중복 처리도 없다.

위의 쿼리는 SET 절의 서브 쿼리가 하나 이하의 값을 리턴해야 하는 전제 조건이 있다.

보다 자세한 내용은 대용량데이터베이스솔루션 2권의 제1장 SQL의 활용 에서 3.3. UPDATE의 활용을 참고.

예:

UPDATE a_table a

   SET ( a1, b1, c1, d1) = ( SELECT a2, b2, c2, d2

                               FROM b_table b

                              WHERE b.col1 = 1

                                AND b.col2 = a.col2 )

 WHERE EXISTS ( SELECT 1

                  FROM b_table b

                 WHERE b.col1 = 1

                   AND b.col2 = a.col2 );

위의 쿼리를 최적화 하면,

UPDATE ( SELECT a1, b1, c1, d1, a2, b2, c2, d2

           FROM a_table a

              , b_table b

          WHERE a.col1 = 1

            AND a.col2 = b.col2 )

   SET a1 = a2

     , b1 = b2

     , c1 = c2

     , d1 = d2;

출처: 지기닷넷