086 서브 쿼리를 사용하여 데이터 수정하기

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

UPDATE	서브쿼리 가능
SET	서브쿼리 가능
WHERE	서브쿼리 가능

---

WHERE절의 서브쿼리

---

문제 486. 사원 테이블에 ALLEN보다 늦게 입사한 사원들의 월급을 9000으로 변경하시오.

UPDATE emp
SET sal = 9000
WHERE hiredate > (SELECT hiredate FROM emp WHERE ename = 'ALLEN');

 

문제 487. SCOTT과 같은 부서번호에서 일하는 사원들의 월급을 8000으로 수정하시오.

UPDATE emp
SET sal = 8000
WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SCOTT');

---

SET절의 서브쿼리

---

문제 488. KING의 월급을 ALLEN의 월급으로 변경하시오.

UPDATE emp
SET sal = (SELECT sal FROM emp WHERE ename = 'ALLEN')
WHERE ename = 'KING';

---

 문제 489. KING의 부서번호를 JAMES의 부서번호로 변경하시오.

UPDATE emp
SET deptno = (SELECT deptno FROM emp WHERE ename = 'JAMES')
WHERE ename = 'KING';

---

문제 490. emp 테이블에 loc컬럼을 추가하시오. 

ALTER TABLE emp
ADD loc VARCHAR2(10);

문제 491. 지금 추가한 loc컬럼에 해당 사원의 부서 위치로 값을 갱신하시오.

(merge문 말고 상호관련 서브쿼리를 이용한 update문으로 수행)

UPDATE emp E
SET loc = (SELECT loc FROM dept D WHERE D.deptno = E.deptno);

 

 14개 행 이(가) 업데이트되었습니다.

---

* emp 테이블의 부서번호가 set절의 서브쿼리에 하나씩 들어가면서 하나씩 갱신하기 때문에 시간이 많이 걸림.

데이터가 적을 경우 이런식으로 서브쿼리를 사용해도 되지만, 데이터가 많은 경우에는 비효율. MERGE문의 속도가 훨씬 빠름.

---

문제 493. deptavg 컬럼에 값을 갱신하는데 해당 사원이 속한 부서번호의 평균월급으로 값을 갱신하시오. 

튜닝 전: 악성SQL

UPDATE emp e
SET deptavg = (SELECT avg(sal) FROM emp s WHERE s.deptno = e.deptno);

SELECT * FROM emp
ORDER BY deptno;

---

문제 494. 위의 SQL을 튜닝하시오.

MERGE INTO emp e
USING (SELECT deptno, avg(sal) as 부서평균 FROM emp GROUP BY deptno) v
ON (e.deptno = v.deptno)
WHEN MATCHED THEN
UPDATE SET e.deptavg = v.부서평균;

설명 :

서브쿼리(v) 부분 결과 

e에 v를 사용해서 merge하는데,

deptno라는 연결고리를 이용해서, 연결고리끼리 값이 같을때,

부서평균이라는 값을 e의 deptavg컬럼에 넣어달라는 의미

---

문제 495. emp 테이블에 grade 컬럼을 추가하시오.

ALTER TABLE emp
ADD grade NUMBER(10);

---

문제 496. salgrade 테이블을 사용하여 emp 테이블에 grade 컬럼 값을 MERGE하는데, 해당 사원의 급여등급으로 값을 merge 하시오.

 

튜닝 후

MERGE INTO emp e
USING salgrade s 
ON (e.sal between s.losal and s.hisal)
WHEN MATCHED THEN
UPDATE SET e.grade = s.grade;

---

문제 497. 위의 merge문을 롤백하고, 상호관련 서브쿼리를 사용한 update문으로 작성하시오.

 

튜닝 전

UPDATE emp e
SET grade = (SELECT grade FROM salgrade s 
                WHERE e.sal BETWEEN s.losal AND s.hisal);