연관성 있는 서브 쿼리 역시 UPDATE, MERGE, DELETE 문에서 사용할 수 있다. 부서 테이블에서 상위 부서가 기획부(부서번호가 90)에 속하는 사원들의 부서별 평균 급여를 조회해 보자.

    입력

        SELECT department_id , AVG(salary)
          FROM employees a
         WHERE department_id IN ( SELECT department_id
                                    FROM departments
                                   WHERE parent_id = 90)
         GROUP BY department_id;
    

    결과

        DEPARTMENT_ID AVG(SALARY)
        ------------- --------------------
        60            5760
        70            10000
        100           8601.33333
        110           10154
    

    이 결과 값을 이용해 상위 부서가 기획부에 속하는 모든 사원의 급여를 자신의 부서별 평균급여로 갱신하는 쿼리는 다음과 같다.

    입력

        UPDATE employees a
           SET a.salary = ( SELECT sal
                              FROM ( SELECT b.department_id, AVG(c.salary) as sal
                                       FROM departments b,
                                            employees c
                                      WHERE b.parent_id = 90
                                        AND b.department_id = c.department_id
                                      GROUP BY b.department_id ) d
                              WHERE a.department_id = d.department_id )
         WHERE a.department_id IN ( SELECT department_id
                                      FROM departments
                                     WHERE parent_id = 90 ) ;
    

    결과

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

    제대로 갱신됐는지 확인해 보자.

    입력

        SELECT department_id , MIN(salary), MAX(salary)
          FROM employees a
         WHERE department_id  IN ( SELECT department_id
                                     FROM departments
                                    WHERE parent_id = 90)
         GROUP BY department_id ;
    

    결과

        DEPARTMENT_ID MIN(SALARY) MAX(SALARY)
        ------------- ----------- -----------
        60            5760        5760
        70            10000       10000
        100           8601.33     8601.33
        110           10154       10154
    

    부서별 최소와 최대 금액이 모두 같고, 위에서 구한 부서별 평균값으로 갱신되었으므로 제대로 처리되었다. UPDATE문에서 연관성 있는 서브 쿼리를 사용하면 위 쿼리처럼 좀 복잡해지는 단점이 있다. 하지만 이를 MERGE문으로 변경하면 훨씬 적은 코드로 깔끔하게 작성할 수 있다.

    입력

        MERGE INTO employees a
          USING ( SELECT b.department_id, AVG(c.salary) as sal
                    FROM departments b,
                         employees c
                   WHERE b.parent_id = 90
                     AND b.department_id = c.department_id
                    GROUP BY b.department_id ) d
              ON ( a.department_id = d.department_id )
         WHEN MATCHED THEN
              UPDATE SET a.salary = d.sal;
    

    결과

        14개 행 이(가) 병합되었습니다.
    

    다시 변경한 데이터를 원 상태로 되돌려 놓도록 ROLLBACK 문을 실행하자.

    입력

        ROLLBACK;
    
    신간 소식 구독하기
    뉴스레터에 가입하시고 이메일로 신간 소식을 받아 보세요.