[오라클, Oracle] DML 문 - UPDATE 문

 

UPDATE 문을 사용하면 테이블의 기존 행을 갱신할 수 있다.

 

UPDATE {table | view | subquery} [t_alias]

SET {column = {expr | (subquery) | DEFAULT} | (column [, column]...) = (subquery)}

[, {column = {expr | (subquery) | DEFAULT} | (column [, column]...) = (subquery)}] ...

WHERE condition;

 

예제를 위해 아래와 같이 테이블을 생성하자.

T1, T2 테이블은 1:M 비식별 관계를 가진다.

 

DROP TABLE T1 PURGE;

DROP TABLE T2 PURGE;

 

CREATE TABLE T1 AS SELECT DEPTNO, DNAME, 0 AS SAL, 0 AS COMM FROM DEPT;

CREATE TABLE T2 AS SELECT EMPNO, ENAME, SAL, COMM, DEPTNO FROM EMP;

 

ALTER TABLE T1 ADD CONSTRAINT T1_PK PRIMARY KEY (deptno);

ALTER TABLE T2 ADD CONSTRAINT T2_PK PRIMARY KEY (empno);

ALTER TABLE T2 ADD CONSTRAINT T2_F1 FOREIGN KEY (deptno) REFERENCES T1 (deptno);

 

아래는 T1 테이블을 조회한 결과다.

SAL과 COMM이 모두 0이다.

 

SELECT * FROM T1;

 

아래 쿼리는 40번 부서의 SAL을 10000, COMM을 1000으로 갱신한다.

 

UPDATE T1 SET SAL = 10000, COMM = 1000 WHERE DEPTNO = 40;

 

아래는 T1 테이블을 조회한 결과다.

40번 부서의 SAL과 COMM이 각각 10000, 1000으로 갱신된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

아래 쿼리는 SET 절에 다중 열 서브 쿼리를 사용했다.

서브 쿼리의 결과로 값이 갱신된다.

 

UPDATE T1 A

SET (A.SAL, A.COMM) =

(SELECT SUM (X.SAL), SUM (X.COMM) FROM T2 X WHERE X.DEPTNO = A.DEPTNO);

 

아래는 T1 테이블을 조회한 결과다.

40번 부서의 SAL과 COMM이 널로 갱신된 것을 확인할 수 있다.

T2 테이블에 DEPTNO가 40인 행이 존재하지 않기 때문에 널로 갱신된 것이다.

 

SELECT * FROM T1;

 

아래와 같이 WHERE 절에 상관 서브 쿼리를 사용하면 불필요한 갱신을 방지할 수 있다.

 

UPDATE T1 A

SET (A.SAL, A.COMM) =

(SELECT SUM (X.SAL), SUM (X.COMM) FROM T2 X WHERE X.DEPTNO = A.DEPTNO)

WHERE EXISTS (SELECT 1 FROM T2 X WHERE X.DEPTNO = A.DEPTNO);

 

UPDATE 문에도 인라인 뷰를 사용할 수 있다.

아래 쿼리는 위 쿼리와 결과가 동일하다.

인라인 뷰를 통해 T2 테이블을 1번만 읽고 갱신을 수행했다.

위 쿼리에 비해 성능 측면에서 효율적이다.

 

UPDATE (SELECT A.SAL, A.COMM, B.SAL AS SAL_N, B.COMM AS COMM_N

FROM T1 A

, (SELECT DEPTNO, SUM (SAL) AS SAL, SUM (COMM) AS COMM

FROM T2

GROUP BY DEPTNO) B

WHERE B.DEPTNO = A.DEPTNO)

SET SAL = SAL_N, COMM = COMM_N;

 

위 쿼리는 11.2 이하 버전에서 "ORA-01779 : 키-보존된 것이 아닌 테이블로 대응한 열을 갱신할 수 없습니다" 에러가 발생한다.

(T2 테이블을 조인 조건인 DEPTNO 열로 그룹핑했기 때문에 조인 차수가 1:1이다. T1 테이블의 단일 행이 단일 값으로 갱신되는 것이 보장되므로 불필요한 에러를 발생시킨 것이다. 12.1 버전부터는 에러가 발생하지 않는다.)

11.2 이하 버전에서는 아래와 같이 MERGE 문을 사용해야 한다.

(10.2 이하 버전에서 BYPASS_UJVC 힌트를 사용하면 에러가 발생하지 않는다. 해당 힌트는 문서화되지 않은 힌트다.

MERGE 문을 사용하는 편이 바람직하다.)

 

MERGE

INTO T1 T

USING (SELECT DEPTNO, SUM (SAL) AS SAL, SUM (COMM) AS COMM

FROM T2

GROUP BY DEPTNO) S

ON (T.DEPTNO = S.DEPTNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL, T.COMM = S.COMM;

 

아래 쿼리는 버전과 관계없이 에러가 발생한다.

갱신할 테이블(T1)과 나머지 테이블(T2)의 조인 차수가 1:M이면, 1쪽 테이블(T1)의 값이 M쪽 테이블(T2)의 값으로 여러 번 갱신될 수 있기 때문이다.

 

UPDATE (SELECT A.SAL, A.COMM, B.SAL AS SAL_N, B.COMM AS COMM_N

FROM T1 A, T2 B

WHERE B.DEPTNO = A.DEPTNO)

SET SAL = SAL_N, COMM = COMM_N;

 

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

 

M쪽 테이블 (T2)의 값을 갱신하면 에러가 발생하지 않는다.

M쪽 테이블(T2)의 값이 1쪽 테이블(T1)의 값을 1번만 갱신되는 것이 보장되기 때문이다.

1:M 조인 차수에서 M쪽 테이블의 행이 늘어나지 않는 것과 동일한 원리다.

 

UPDATE (SELECT A.SAL, A.COMM, B.SAL AS SAL_N, B.COMM AS COMM_N

FROM T2 A, T1 B

WHERE B.DEPTNO = A.DEPTNO)

SET SAL = SAL_N, COMM = COMM_N;

 

아래와 같이 T1 테이블의 PK 제약 조건을 삭제해보자.

 

ALTER TABLE T1 DROP CONSTRAINT T1_PK CASCADE;

 

동일한 쿼리를 수행하면 에러가 발생한다.

T1 테이블에 PK 제약 조건이 없기 때문에 T2 테이블의 값이 1번만 갱신되는 것을 보장할 수 없기 때문이다.

 

UPDATE (SELECT A.SAL, A.COMM, B.SAL AS SAL_N, B.COMM AS COMM_N

FROM T2 A, T1 B

WHERE B.DEPTNO = A.DEPTNO)

SET SAL = SAL_N, COMM = COMM_N;

 

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

 

아래 쿼리는 버전과 관계없이 정상적으로 동작한다.

T1 테이블과 DEPT 테이블이 DEPTNO 열로 등가 조인되기 때문에 조인 차수가 1:1이다.

T1 테이블의 단일 행이 단일 값으로 갱신되는 것이 보장된다.

 

UPDATE (SELECT A.*, B.DNAME AS DNAME_N

FROM T1 A, DEPT B

WHERE B.DEPTNO = A.DEPTNO)

SET DNAME = DNAME_N;

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.