연관성 있는 서브 쿼리 역시 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;