오라클 업데이트 문 사용 시 자주 사용할 수 있는 서브쿼리, 머지 형태 예시자료입니다.
예시에서 TA 테이블이 업데이트 타깃이고, TB 테이블은 업데이트 시 참고하려는 소스 테이블입니다. 그리고 두 테이블 간의 조인할 수 있는 키는 총 7개(각각 컬럼 이름이 다음과 같습니다. E, C, V, D, O, L, U)로 가정합니다.
업데이트 레프트 조인 인라인 뷰
첫 번째 방법은 TA 테이블을 TB 테이블과 레프트 조인한 인라인 뷰에 대하여 업데이트를 시도합니다. 해당의 경우 키 보존이 이루어져야 합니다. 키 보존이 아닌 경우에는 다음의 오류가 발생합니다.
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다
ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> UPDATE (SELECT *
2 FROM TA
3 LEFT JOIN
4 TB
5 ON 0 = 0
6 AND BE = AE
7 AND BC = AC
8 AND BV = AV
9 AND BD = AD
10 AND BO = AO
11 AND BL = AL
12 AND BU = AU)
13 SET AI = BI;
SET AI = BI
*
13행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다업데이트 서브쿼리 (상관 하위쿼리 : Correlated subquery)
두 번째 방법은 서브쿼리를 이용하는 방법입니다. 아마 가장 많이 사용하는 방법이 아닐까 합니다. WHERE 이하 부분을 생략하면 TA 테이블의 모든 행에 대해 실행하는 부분을 기억해 주세요.
SQL> UPDATE TA
2 SET AI =
3 (SELECT BI
4 FROM TB
5 WHERE 0 = 0
6 AND BE = AE
7 AND BC = AC
8 AND BV = AV
9 AND BD = AD
10 AND BO = AO
11 AND BL = AL
12 AND BU = AU)
13 WHERE EXISTS
14 (SELECT NULL
15 FROM TB
16 WHERE 0 = 0
17 AND BE = AE
18 AND BC = AC
19 AND BV = AV
20 AND BD = AD
21 AND BO = AO
22 AND BL = AL
23 AND BU = AU);
99 행이 갱신되었습니다.머지 문으로 업데이트
세 번째 방법은 머지 문장으로 매치가 되는 경우에만 업데이트 하도록 합니다.
SQL> MERGE INTO TA
2 USING TB
3 ON ( 0 = 0
4 AND BE = AE
5 AND BC = AC
6 AND BV = AV
7 AND BD = AD
8 AND BO = AO
9 AND BL = AL
10 AND BU = AU)
11 WHEN MATCHED
12 THEN
13 UPDATE SET AI = BI;
99 행이 병합되었습니다.