[Oracle]merge문을 이용하여 WINDOW FUNCTION 에서 나온 자료를 Update 데이터로 반영하기

SQL> select * from student order by class, id;

CLASS      ID              SCORE    RANKING
---------- ---------- ---------- ----------
1-1        11                 78
1-1        12                 67
1-1        13                 88
1-1        14                 69
1-1        15                 78
1-1        16                 63
1-1        17                 84
1-1        18                 92
1-2        11                 86
1-2        12                 67
1-2        13                 94
1-2        14                 87
1-2        15                 72

 

13 개의 행이 선택되었습니다.

 

SQL> MERGE INTO student A
  2      USING
  3      (WITH v_student
  4            AS (SELECT CLASS, ID,
  5                       rank() over (partition by CLASS  order by score) NEW_RANK
  6                  FROM STUDENT
  7                 WHERE CLASS like '1%'
  8                )
  9                SELECT * FROM v_student
 10      ) B
 11      ON (A.CLASS = B.CLASS AND A.ID = B.ID)
 12   WHEN MATCHED THEN
 13     UPDATE SET A.RANKING = B.NEW_RANK;

 

13 행이 병합되었습니다.

 

SQL> SELECT * FROM student order by class, ranking;

CLASS      ID              SCORE    RANKING
---------- ---------- ---------- ----------
1-1        16                 63          1
1-1        12                 67          2
1-1        14                 69          3
1-1        11                 78          4
1-1        15                 78          4
1-1        17                 84          6
1-1        13                 88          7
1-1        18                 92          8
1-2        12                 67          1
1-2        15                 72          2
1-2        11                 86          3
1-2        14                 87          4
1-2        13                 94          5

13 개의 행이 선택되었습니다.

 

 

* line 3: WITH문을 이용하여 v_student 라는 subQuery에서 window function을 이용하여 원하는 결과치의 쿼리를 작성한다.

* line 11: student table과 v_student를 조인한다.

* line 13: student 의 ranking에 v_student의 new_rank가 update 된다.

 

 

※ MERGE 문은 Oracle 9i부터 나왔으며,

    9i때는 WHEN NOT MATCHED THEN 에 대한 내용이 없으면 에러가 난다고 한다.

    10g 이상에서는 오류없이 잘 수행된다.