[MSSQL] UPDATE 쿼리에서의 Alias 사용 및 RANK 함수

가끔 UPDATE 쿼리를 사용할 때 FROM 절에서의 JOIN을 어떻게 해야 할 지 고민을 합니다. 업무상 쿼리, 프로시저를 많이 다루긴 하지만, 뭐든지 자주 접해보지 않으면 할 때마다 아리까리합니다. 그래서 좀 정리해 보기로 했습니다.

기본적인 구문은 이런 식이죠.
UPDATE 테이블명 SET 컬럼명 = '값' WHERE 어쩌고 저쩌고.

하지만, 조건이 까다로워지고, 다른 테이블과 JOIN 을 해야할 경우가 생기고 또 종종 자기 스스로 JOIN (SELF JOIN)을 해야 하는 경우가 생깁니다. 다음 예문을 만들면서 RANK 함수에 대한 SQL 2000 버전 호환이 안됨 등등 주로 하고자 했던 내용이 아닌 게 들어가긴 했습니다만, UPDATE 문의 예제를 들면서 UPDATE 문을 사용하는 당위성(?)을 찾다보니 들어간 내용입니다. RANK 함수에 대한 설명은 따로 하진 않겠습니다. 그냥 고대로 복사해서 테스트 해 보면 됩니다.

* 순위는 무슨 순위일까요? "소주랑"이 1등인데...

1. CREATE TABLE TBL_SCORE
2. (
3.   PLAYER  NVARCHAR(30),
4.   SCORE   NUMERIC(5,2)
5. )
6. GO
7.  
8. INSERT TBL_SCORE VALUES ('정우성',  90 )
9. INSERT TBL_SCORE VALUES ('이병헌',  80 )
10. INSERT TBL_SCORE VALUES ('장동건',  95 )
11. INSERT TBL_SCORE VALUES ('소주랑', 100 )
12. INSERT TBL_SCORE VALUES ('이성재',  85 )
13. INSERT TBL_SCORE VALUES ('정준호',  88 )
14. INSERT TBL_SCORE VALUES ('유재석',  75 )
15. INSERT TBL_SCORE VALUES ('권지용',  88 )
16. GO
17.  
18. -- 점수내림차순으로 SELECT
19. SELECT TBL_SCORE.PLAYER AS PLAYER,
20.        TBL_SCORE.SCORE AS SCORE
21.   FROM TBL_SCORE
22.  ORDER BY SCORE DESC
23. GO
24.  
25. -- SQL 2005 이상에서만 작동하는
26. -- 랭킹 매기면서 점수 내림차순(랭킹 오름차순)으로 정렬
27. SELECT RANK() OVER (ORDER BY SCORE DESC) AS RANKING,
28.        TBL_SCORE.PLAYER AS PLAYER,
29.        TBL_SCORE.SCORE AS SCORE
30.   FROM TBL_SCORE
31.  ORDER BY RANKING ASC
32. GO
33.  
34. -- SQL 2005 이전 버전을 위한 랭킹
35. -- 랭킹 매기면서 점수 내림차순(랭킹 오름차순)으로 정렬
36. SELECT ISNULL(COUNT(C.PLAYER), 0) + 1 AS RANKING,
37.        T.PLAYER AS PLAYER,
38.        T.SCORE AS SCORE
39.   FROM TBL_SCORE AS T LEFT OUTER JOIN
40.                       TBL_SCORE AS C ON C.SCORE > T.SCORE
41.  GROUP BY T.PLAYER, T.SCORE
42.  ORDER BY RANKING ASC
43. GO
44.  
45. -- 컬럼을 추가해 보자
46. ALTER TABLE TBL_SCORE ADD RANKING INT
47. GO
48.  
49. -- 해당 RANKING 업데이트
50. UPDATE T -- 여기!!
51.    SET RANKING = C.RANKING
52.   FROM TBL_SCORE AS T INNER JOIN
53.        ( SELECT T.PLAYER,
54.                 ISNULL(COUNT(C.PLAYER), 0) + 1 AS RANKING
55.            FROM TBL_SCORE AS T LEFT OUTER JOIN
56.                                TBL_SCORE AS C ON C.SCORE > T.SCORE
57.           GROUP BY T.PLAYER, T.SCORE ) AS C ON C.PLAYER = T.PLAYER
58. GO
59.  
60. -- 결과 SELECT
61. SELECT TBL_SCORE.RANKING AS RANKING,
62.        TBL_SCORE.PLAYER AS PLAYER,
63.        TBL_SCORE.SCORE AS SCORE
64.   FROM TBL_SCORE
65.  ORDER BY RANKING ASC
66. GO
67.  
68. DROP TABLE TBL_SCORE
69. GO

제가 이야기하고자 했던 것의 결론을 간단히 말씀드리자면, 이겁니다.

1. UPDATE T
2.    SET RANKING = 1
3.   FROM TBL_SCORE AS T
4. GO

UPDATE 대상 테이블에 Alias(별칭)를 주면서 구문을 작성할 때에는 이렇게 하면 좀 쉽다... 라는 거.