(oracle) 다른 테이블 값 참조해서 update 해주기

기본적으로 쿼리를 짜다보면 b 테이블에 있는 값을 불러오든 처리해서 불러오든 가져와야 하는 경우가 있다.

 

프로시저를 짜는 것도 하나의 방법이지만 비효율적이고 시간도 더 나오는 것 같다.(테스트 해보니 최소 1.4배...?)

 

update 문을 좀 더 활용해서 해보면 구조는 아래와 같다.

update 테이블A a
set (a.칼럼1, a.칼럼2, a.칼럼3) = (select sum(b.칼럼1)
										  , sum(b.칼럼2)
                                          , sum(b.칼럼3)
                                     from 테이블B b
                                     where b.키값1 = a.키값1
                                       and b.키값2 = a.키값2)
where a.조건1 = '조건값1'
  and a.조건2 = '조건값2'

위 update 문에서 좀 더 좋은 방법은 아래와 같다.(시간 단축 튜닝)

update (
		select a.칼럼1 as a_칼럼1,
               b.칼럼1 as b_칼럼1,
               a.칼럼2 as a_칼럼2,
               b.칼럼2 as b_칼럼2
          from 테이블A a inner join 테이블 B
                            on a.key = b.key
          where a.조건1 = '조건값1'
		)
set a_칼럼1 = b_칼럼1
    a_칼럼2 = b_칼럼2

위 쿼리는 Updatable join view를 생각할 수 있다.

조인되는 테이블은 1:1 또는 1:N의 관계를 가져야 한다. Update되는 컬럼의 테이블은 N쪽이여야만 한다.

여기서 한가지 주의할 점은 테이블 B의 컬럼에 Unique 인덱스가 없으면

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

에러가 발생한다. 10g까지는 /*+ BYPASS_UJVC */ 힌트를 사용하면 됐지만 11g부터는 불가능하다.

12g부터는 테이블 B를 group by 해서 그룹칼럼을 unique하게 인식해서 unique 키가 없어도 가능하다.

(키 걸어줘도 되는 테이블이면 그냥 unique 걸어주자...)

ALTER TABLE [TABLE명] ADD CONSTRAINT [PK명] PRIMARY KEY ([KEY1],[KEY2],[...]);

 

 

뭐 프로시저로 간단하게 표현해보자면

declare
	p_임시값1 테이블A.칼럼1%type;
    p_임시값2 테이블A.칼럼2%type;
    p_임시값3 테이블A.칼럼3%type;
Begin
	select sum(b.칼럼1)
    	   , sum(b.칼럼2)
           , sum(b.칼럼3)
      into p_임시값1
      	   , p_임시값2
           , p_임시값3
      from 테이블 B b
      where 조건;
      
      update 테이블 A
      set   a.칼럼1 = p_임시값1,
      		a.칼럼2 = p_임시값2,
            a.칼럼3 = p_임시값3
      where 조건;
END;
      

위와 같고 아니면 그냥 merge 문법을 써도 무방하겠다.