오라클 UPDATE 다른 테이블 참조 - olakeul UPDATE daleun teibeul chamjo

다른 테이블의 SELECT 한 결과물을 참조하여 현재 테이블을 UPDATE 해야 하는 상황이 있습니다.

Oracle, MS-SQL, Maria DB, Mysql DBMS마다 다른 문법을 지원하므로 방법도 각자 다릅니다.

저는 Oracle을 사용 중인데 다른 DBMS 보다 조금 문법이 어려운 것 같네요 이 방법에 한해서는요 ㅎㅎ

table master, table user 두 개의 테이블이 있다고 가정하겠습니다.

master 테이블의 Status을 user 테이블의 Status의 값을 참조하여 변경하는 쿼리를 작성해보겠습니다.

MS-SQL

UPDATE master SET master.status = cust.status FROM master m INNER JOIN cust c ON m.id = c.id;

MySQL and MariaDB

UPDATE master m, cust c SET m.status = c.status WHERE m.id = c.id;

Oracle

단순 업데이트만 사용하는 방법

EXISTS를 사용하지 않으면 첫 번째 서브 쿼리의 id 키 값이 매칭 되지 않는 값들은 master의 status 값이 null로 변경됩니다. 

UPDATE master m SET m.status = (select c.status from cust c where m.id = c.id) WHERE EXISTS (select 1 from cust c where m.id = c.id)

merge into를 구문을 사용하는 방법

MERGE INTO master m USING cust c ON (m.id = c.id) WHEN MATCHED THEN UPDATE SET m.status = c.status;

Oracle 실습

master table

cust table

이 쿼리를 실행해보겠습니다.

UPDATE master m SET m.status = (select c.status from cust c where m.id = c.id) WHERE EXISTS (select 1 from cust c where m.id = c.id)

우리가 기대하는 결과는 master 테이블의 test1, test3, test5의 status값이 FALSE로 변환되는 결과입니다.

update 결과

올바르게 변환됩니다!

그런데 만약 아래와 같이 EXIST 구문을 빼면 어떻게 될까요?

UPDATE master m SET m.status = (select c.status from cust c where m.id = c.id)

update 결과

test2와 test4는 id 키 값이 매칭되는 것이 없으므로 status의 값이 null로 변경됩니다. 기본적으로 update 구문에서 where 조건을 설정하지 않으면 전체 테이블을 update하게 되어서 나타나는 결과입니다.

merger into 쿼리의 결과 역시 제대로 업데이트 되는 것을 볼 수 있습니다!

MERGE INTO master m USING cust c ON (m.id = c.id) WHEN MATCHED THEN UPDATE SET m.status = c.status;

merge into 결과

기본적으로 쿼리를 짜다보면 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 문법을 써도 무방하겠다.