유용 update문

테스트 테이블 확인.

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 문은 신중을 기해야 할 것입니다. 검증은 중요한 것이니깐요^^

위 쿼리문 중 마지막 쿼리문이 가장 낫다고는 하였지만 어느 것 하나 정답은 없다고 생각합니다.