소스: sql-src/adv_10_multi_dml/01_correlated_update.sql. 표는 셋입니다. emp 는 사원(급여 sal, 보너스 bonus 는 기본 0), raise_rate 는 부서별 인상률(pct)과 보너스 금액, resign 은 퇴사자 사원 번호입니다. 경영·인사 부서는 raise_rate 에 없고, 기획은 raise_rate 에만 있습니다.
ID | NAME | DEPT | SAL | BONUS
---+--------+------+-----+------
1 | 김대표 | 경영 | 900 | 0
2 | 김개발 | 개발 | 500 | 0
3 | 남개발 | 개발 | 450 | 0
4 | 이영업 | 영업 | 400 | 0
5 | 박영업 | 영업 | 380 | 0
6 | 최인사 | 인사 | 350 | 0
(6행)DEPT | PCT | BONUS_AMT
-----+-----+----------
개발 | 10 | 50
기획 | 7 | 30
영업 | 5 | 20
(3행)위가 emp, 아래가 raise_rate 입니다. resign 에는 5 번 한 건이 들어 있습니다.
급여를 인상률만큼 올립니다. WHERE 가 없어 6명 전체가 대상입니다.
UPDATE emp
SET sal = sal + sal * (SELECT r.pct FROM raise_rate r WHERE r.dept = emp.dept) / 100;ID | NAME | DEPT | SAL
---+--------+------+-----
1 | 김대표 | 경영 | NULL
2 | 김개발 | 개발 | 550
3 | 남개발 | 개발 | 495
4 | 이영업 | 영업 | 420
5 | 박영업 | 영업 | 399
6 | 최인사 | 인사 | NULL
(6행)raise_rate 에 없는 경영·인사 부서는 서브쿼리가 NULL 이라 급여가 통째로 NULL 이 됐습니다. 오류도 경고도 없이 6행이 처리되므로 결과를 봐야 알 수 있습니다. 실무에서는 트랜잭션 안에서 실행하고 건수를 확인한 뒤 커밋합니다.
초기 데이터로 되돌린 뒤 짝 있는 행만 고칩니다.
UPDATE emp
SET sal = sal + sal * (SELECT r.pct FROM raise_rate r WHERE r.dept = emp.dept) / 100
WHERE EXISTS (SELECT 1 FROM raise_rate r WHERE r.dept = emp.dept);ID | NAME | DEPT | SAL
---+--------+------+----
1 | 김대표 | 경영 | 900
2 | 김개발 | 개발 | 550
3 | 남개발 | 개발 | 495
4 | 이영업 | 영업 | 420
5 | 박영업 | 영업 | 399
6 | 최인사 | 인사 | 350
(6행)실행 메시지는 4행 처리였고, 경영·인사는 원래 값을 지켰습니다. 서브쿼리가 두 번 나오는 것이 번거롭지만 표준에서는 이 형태가 기본입니다.
급여와 보너스를 함께 바꿉니다. SET (a, b) = (SELECT ...) 형태는 Oracle 과 H2 에서 실행됩니다.
UPDATE emp
SET (sal, bonus) = (SELECT emp.sal + emp.sal * r.pct / 100, r.bonus_amt FROM raise_rate r WHERE r.dept = emp.dept)
WHERE EXISTS (SELECT 1 FROM raise_rate r WHERE r.dept = emp.dept);ID | NAME | DEPT | SAL | BONUS
---+--------+------+-----+------
1 | 김대표 | 경영 | 900 | 0
2 | 김개발 | 개발 | 550 | 50
3 | 남개발 | 개발 | 495 | 50
4 | 이영업 | 영업 | 420 | 20
5 | 박영업 | 영업 | 399 | 20
6 | 최인사 | 인사 | 350 | 0
(6행)서브쿼리 하나로 두 값을 얻으므로 조회가 한 번입니다. 컬럼마다 서브쿼리를 따로 쓰면 같은 조회를 컬럼 수만큼 반복합니다. 이 문법은 MySQL·MSSQL 에는 없어서 컬럼마다 쓰거나 조인 UPDATE 를 씁니다.
resign 에 번호가 있는 사원을 지우고, 사원이 한 명도 없는 부서의 인상률 행을 지웁니다.
DELETE FROM emp
WHERE EXISTS (SELECT 1 FROM resign r WHERE r.emp_id = emp.id);ID | NAME | DEPT
---+--------+-----
1 | 김대표 | 경영
2 | 김개발 | 개발
3 | 남개발 | 개발
4 | 이영업 | 영업
6 | 최인사 | 인사
(5행)5 번 박영업이 삭제됐습니다. 다음은 NOT EXISTS 입니다.
DELETE FROM raise_rate
WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE e.dept = raise_rate.dept);DEPT | PCT | BONUS_AMT
-----+-----+----------
개발 | 10 | 50
영업 | 5 | 20
(2행)사원이 없던 기획 행이 지워졌습니다. NOT IN 은 서브쿼리 값에 NULL 이 있으면 결과가 비므로(중급 02 EXISTS·IN) 이런 삭제에는 NOT EXISTS 가 안전합니다.