유용 update문

2009. 12. 9. 15:00Oracle/Oracle Tip

반응형

테스트 테이블 확인.

SQL> select deptno, max(sal) from emp group by deptno;

   DEPTNO  MAX(SAL)
---------- ----------
            30         2850
            20         3000
            10         5000

SQL> select * from dept_test;

    DEPTNO DNAME                   LOC                    SAL
---------- ------------------- ----------- ----------
            10 ACCOUNTING           NEW YORK          
            20 RESEARCH              DALLAS              
            30 SALES                    CHICAGO             2850
            40 OPERATIONS           BOSTON
            -1 DBA                        Seoul
            40 SALES                    CHICAGO
            50 SALES                    CHICAGO             1111

7 rows selected.

무심코 지나쳐 버릴 것 같은 update문을 재연해 보았습니다.
아래와 같은 쿼리문을 가끔 날려서 실수를 범하는 경우가 있어서 간단하게 정리를 해보았습니다.

SQL> update dept_test a 
     2   set sal = (select max(sal) from emp b where a.deptno=b.deptno group by deptno);

7 rows updated.

SQL> select * from dept_test;

   DEPTNO DNAME                   LOC                    SAL
---------- ------------------- ----------- ----------
            10 ACCOUNTING           NEW YORK          5000
            20 RESEARCH              DALLAS              3000
            30 SALES                    CHICAGO             2850
            40 OPERATIONS           BOSTON
            -1 DBA                        Seoul
            40 SALES                    CHICAGO
            50 SALES                    CHICAGO

7 rows selected.   --> 위 update 조건에 맞지 않는 것은 null값으로 처리가 되네요.

SQL> rollback;

Rollback complete.


<해결방안>

아래 사항들은 가끔 제가 사용하는 것입니다. 방법은 여러가지가 있겠죠^^;
(오래전에 구글링으로 찾아서 update시 유용하게 사용하고 있어서 정리를 합니다.)

SQL> update dept_test a 
     2  set sal = (select max(sal) from emp b where a.deptno=b.deptno group by deptno) 
     3  where deptno in (select deptno from emp group by deptno);  

3 rows updated. 

SQL> select * from dept_test;

  DEPTNO DNAME                   LOC                    SAL
---------- ------------------- ----------- ----------
            10 ACCOUNTING           NEW YORK          5000
            20 RESEARCH              DALLAS              3000
            30 SALES                    CHICAGO             2850
            40 OPERATIONS           BOSTON
            -1 DBA                        Seoul
            40 SALES                    CHICAGO
            50 SALES                    CHICAGO             1111

7 rows selected. 
          --> 테이블을 2번 읽어야 하고 데이터가 많을 경우 시간이 많이 걸리는 단점이 있어요.
                예전에 한도끝도 없이 기다렸다가 결국에는 취소를 시켰다는..ㅠㅠ

SQL> rollback;

Rollback complete.

SQL> update dept_test a 
     2  set sal = nvl((select max(sal)
     3                      from emp b where a.deptno=b.deptno group by deptno), a.sal);

7 rows updated.     --> 업데이트 할 값이 null 이라면 원래 값을 넣어줌.

SQL> select * from dept_test;

    DEPTNO DNAME                   LOC                    SAL
---------- ------------------- ----------- ----------
            10 ACCOUNTING           NEW YORK          5000
            20 RESEARCH              DALLAS              3000
            30 SALES                    CHICAGO             2850
            40 OPERATIONS           BOSTON
            -1 DBA                        Seoul
            40 SALES                    CHICAGO
            50 SALES                    CHICAGO             1111

7 rows selected. --> 불필요한 로우까지 업데이트를 하는 단점이 있습니다.

SQL> rollback;

Rollback complete.

SQL> update (select a.sal a_sal, b.sal b_sal
  2  from dept_test a, (select deptno, max(sal) sal from emp group by deptno) b
  3  where a.deptno=b.deptno)
  4  set a_sal=b_sal;
set a_sal=b_sal
    *
ERROR at line 4:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다  --> 음..에러가 나네요


SQL> update /*+bypass_ujvc*/(select a.sal a_sal, b.sal b_sal    --> 힌트절 사용
  2  from dept_test a, (select deptno, max(sal) sal from emp group by deptno) b
  3  where a.deptno=b.deptno)
  4* set a_sal=b_sal
SQL> /

3 rows updated.

SQL> select * from dept_test;

    DEPTNO DNAME                   LOC                    SAL
---------- ------------------- ----------- ----------
            10 ACCOUNTING           NEW YORK          5000
            20 RESEARCH              DALLAS              3000
            30 SALES                    CHICAGO             2850
            40 OPERATIONS           BOSTON
            -1 DBA                        Seoul
            40 SALES                    CHICAGO
            50 SALES                    CHICAGO             1111

7 rows selected.    --> 위 3개 쿼리중 실용성있는 쿼리문이라고 합니다.(그냥 그렇다네요ㅋㅋ)

SQL>

<결론>
가끔 나의 짧은 지식으로 검증도 해보지 않고, 무턱대고 commit을 실행 시키는 실수로 인해서 감당할 수 없는 큰 일이 발생할 수가 있습니다. 다른 것도 꼼꼼히 살펴봐야하겠지만 특히나 update 문은 신중을 기해야 할 것입니다. 검증은 중요한 것이니깐요^^
위 쿼리문 중 마지막 쿼리문이 가장 낫다고는 하였지만 어느 것 하나 정답은 없다고 생각합니다.

반응형

'Oracle > Oracle Tip' 카테고리의 다른 글

long형 replace  (0) 2011.01.24
LONG과 CLOB 에 대한 데이터 이전.  (0) 2011.01.04
tablespace 생성 스크립트  (0) 2010.04.30
테이블 read only 설정  (0) 2010.01.27
1년치 날짜 생성.  (0) 2010.01.14