SQL 서브쿼리를 이용한 DATA 수정 Update (update 절에 튜닝시 merge 만큼빠름)

update 문에 서브쿼리를 쓸 수 있는 절

update 테이블명

set 변경할 컬럼과 값

where 조건

→ 서브쿼리절은 , update, set, where 절 3개 모두 가능하다.

 

여러개의 데이터를 한번에 갱신할 수 있는 SQL / 많은 양의 데이터 갱신할때는 튜닝된SQL 을 작성해야된다.

******* 튜닝된 SQL 이란  merge 문 과 update 절의 subqery 문 이다.

 

(1) where 절에 서브쿼리를 쓰는경우 

예제: 스미스 보다 더많은 월급을 받는 사원들의 부서번호를 10번으로 수정

update emp
set deptno = 10
where sal < (select sal
             from emp
             ename = 'SMITH');

문제  :ALLEN 보다 늦게입사한 사원들 커미션을 9000으로 수정

update emp
set comm=9000
where hiredate > (select hiredate
                  from emp
                  where ename ='ALLEN');

(2) SET 절에 서브쿼리를 쓰는경우 

문제 432 :allen 의 직업을 jones 의 직업으로 바꾸시오

UPDATE EMP
SET JOB =( select job
           from emp
           where ename = 'JONES')
WHERE ENAME = 'ALLEN';

 

→ 만약 SET 절의 서브쿼리가 사용하지 못한다면 jones 의 월급을 먼저 쿼리해서 알아내고나서 update 문을 수행하는 두번 작업해야한다.

→ 하지만 서브쿼리절을 입력해서 쿼리문을 작성하면 한번에 실행 시킬 수 있다.

(3) where, set  절 동시에 서브쿼리를 쓰는경우 

문제  ALLEN 보다 늦게 입사한 사원들 월급을 KING의 월급으로 수정

update emp
set sal = (select sal
           from emp
           where ename ='KING')
update hiredate > ( select hiredate 
                   from emp
                   where ename ='ALLEN');

 

(4) 테이블은 같고 빈 컬럼에 특정 컬럼을 그대로 업데이트 하는경우

문제  emp 월급을 sal2 에 그대로 업데이트

alter table emp
add sal2 number(10);

update emp
set sal2 = sal ;

-> 먼저 emp 테이블에 sal2 의 테이블이 없으므로 생성시켜준다. 

후에 set sal2 = sal 이라는 절을 적어 그대로 업데이터 시켜 주면 된다. (같은 테이블이기때문에 이렇게 작성하면된다.)

만약 set sal2 = (select sal from emp) ; 라고 서브쿼리로 적게되면 오류가 뜬다. 

(5) update 절에 서브쿼리를 쓰는경우 

→ 데이터 갱신 속도가 merge 문 만큼 빠르다.

→ 그래서 주로 SQL 튜닝시 Update 절 Subqery 를 많이 사용한다.

→ subqery 를 안쓰는 경우는 view 를 만들어서 쓰는경우이다.

→ primary key 제약을 거는게 단점이나 21 c 버전은 안걸어도 되는듯.

→ primary 걸기싫으면 merge 문 으로 쓰면 된다

문제 : emp 테이블에 부서테이블에 존재하는 loc 컬럼을 그대로 갱신하시오 (튜닝된 sql)

merge 문 만큼이나 실행속도가 빠르다. 

alter table emp
add loc varchar2(10);

alter tabel dept
add constraint dept_deptno_pk primary key (deptno);


update (
         select e.ename, e.loc as emp_loc , d.loc as dept_loc
         from emp e, dept d
         where e.deptno = d.deptno
         )
set emp_loc = dept_loc ;

(1) 일단 loc 라는 컬럼이 emp 테이블에는 없기때문에 생성을 해준다.

(2) dept 에 pk 제약을 걸어주어야한다. 입력시켜줄 데이터가 보존 되지 않는다면 대응되는 열이 불안정해 지기 때문이다. (그러나 오라클 21c 는 제약걸지 않아도 update 가능하다) 

(3) 마지막으로 update절 의 서브쿼리문을 작성하여 쿼리를 실행 해준다. 

서브쿼리문에는 반드시 별칭이 있어야 나중에 set 절 쓸때 바로 적용시킬 수 있다. 

 

방금 본 update 절의 서브쿼리는 좋은 쿼리인데 악성쿼리도 한번 보겠다.

update emp e
set loc = (select loc
           from dept d
           where d.deptno = e.deptno);

악성인 이유 

update 절에 있는 emp 테이블의 부서번호가 첫번째 행부터 건수대로 서브쿼리로 들어오게 된다. 
 예를들어 첫번째 행인 king 의 dept 테이블의 부서위치가 NEW YORK 이면 emp 테이블의 loc 의 데이터를 NEW YORK 으로 KING 의 부서위치를 갱신합니다.
 그런데 이런 갱신이 KING 한명만 있는게 아니라  emp 테이블에 있는모든 사원들에 대해서 갱신해줘야 하므로
 사원 테이블의 건수가 1억이면 1억번 갱신해야한다.

 그래서 튜닝 방법은 merge 문을 사용하거나 아니면 위의 update 절의 서브쿼리를 사용해서
 튜닝하는게 제일 빠르다. 

문제  emp14 테이블의 price 컬럼에 값을 갱신하는데 telecom_service 의 해당 통신사의 price 값을 갱신하시오

alter table telecom_servie 
add constraint telecom_servie_pk primary key(telecom);

update (select e.ename, e.price as emp_price , t.price as service_price
        from emp14 e, telecom_service t 
        where e.telecom = t.telecom ) 
set emp_price = service_price