[MSSQL] 중복 데이터 삭제하기

- GROUP BY HAVING 을 이용한 중복 데이타 삭제

중복 데이타가 있나 없나 체크만 하면 뭐하나? 중복 데이타를 삭제할 수도 있어야지? 하시는 분들을 위해서
앞의 예제에서 사용되었던, 쿼리문을 이용해서 간단한 중복 데이타 삭제 쿼리를 만들어 보겠습니다.
중복 테이타가 있는 테이블은 앞의 예제에서 사용되었던 "loginLog_tbl" 을 그대로 사용하는 것으로 하겠습니다.

"loingLog_tbl"의 uid 컬럼을 기준으로 한다면 kimchi 와 mfcchang 이 각 2회, 3회 중복되어 있고 아래와 같은 쿼리문을 사용해서 보다 정확히 파악할 수 있었습니다.

SELECT uid, count(*) AS loginCount FROM loginLog_tbl GROUP BY uid HAVING count(*) > 1

출력된 결과는 아래와 같습니다. 데이타가 딸랑 9개 인지라 눈으로 파악한 거랑 차이가 없습니다.

 

우리는 이 테이블에서 kimchi 와 mfcchang 의 uid 값중 seq 컬럼이 Max 값이 아닌 값을 지우도록 하겠습니다. 결국 기준이 되는 컬럼은 seq 컬럼입니다. 아래와 같이 SELECT 절 앞부분에 Max(seq) 부분을 추가하여 중복 데이타 중 seq 가 가장 큰 데이타를 SELECT 해 옵니다.

SELECT Max(seq) AS seq, uid, count(*) AS loginCount FROM loginLog_tbl GROUP BY uid HAVING count(*) > 1

그리고 아래와 같이 위의 쿼리문과 loginLog_tbl 자신을 self 조인해 보겠습니다.

위의 쿼리문은 중복 데이타의 seq 값중 가장 큰 값을 제외한 seq 값들 중에 A.uid 와 B.uid 가 같은 것을 SELECT 하라는 뜻입니다. 한눈에 파악 가능하실 겁니다. 이로서 중복 데이타 중 가장 큰 값을 제외한 데이타를 가져오는 쿼리문은 끝났습니다. 하지만 바로 여기에다 DELETE 명령어를 붙이면 에러가 납니다. 왜 에러가 나는지는 다들 잘 아시리라 믿습니다.

SELECT a.seq AS seq
FROM loginLog_tbl A INNER JOIN ( SELECT MAX(seq) AS seq, uid, count(*) AS loginCount FROM loginLog_tbl
GROUP BY uid HAVING count(*) >1) B
ON A.uid = B.uid and A.seq <> B.seq

DELETE 문을 붙이기 위해서 아래와 같이 수정합니다.

위의 쿼리문이 중복 데이타를 삭제하는 최종 쿼리문입니다. 실행하면 loginLog_tbl 에서 3개의 행이 삭제되었다는 메시지가 출력될 것입니다.

 

DELETE FROM loginLog_tbl
WHERE seq IN (
SELECT a.seq AS seq
FROM loginLog_tbl A INNER JOIN ( SELECT MAX(seq) AS seq, uid, count(*) AS loginCount FROM loginLog_tbl
GROUP BY uid HAVING count(*) >1) B
ON A.uid = B.uid and A.seq <> B.seq )

출처:http://www.webmadang.net/database/database.do?action=read&boardid=4001&page=1&seq=6

작성자 : 다자래(mfcchang@naver.com)