MySQL 중복 데이터 찾아서 삭제하기

MySQL DB작업을 하다보면 table에 포함된 중복된 데이터를 골라내서 삭제하고 싶을때가 있다.

revenue라는 테이블안에 collect_date, currency, amount라는 세개의 필드가 있다고 가정할때, collect_date와 currency가 동일하다면 중복된 데이터라고 가정한다.

샘플 데이터

2017-01-01, KRW, 10
2017-01-01, KRW, 10
2017-01-01, USD, 2
2017-01-02, KRW, 34
2017-01-01, USD, 2

위 데이터에서 2017-01-01, KRW, 10 가 중복된 것을 볼 수있다. 중복 데이터를 제거하려면 어떻게 해야할까?

1차 시도: 실패

DELETE
FROM revenue 
WHERE id IN 
(
    (SELECT id FROM revenue GROUP BY collect_date, currency HAVING count(*) > 1)
) 

• GROUP BY 쿼리를 이용하여 데이터를 aggregate 한 후에 중복된 데이터가 하나 이상 존재하는 경우를 찾는다.
• 찾아진 데이터의 id 값을 SELECT해서 DELETE한다.

이렇게 단순히 접근하면 아래와 같은 에러를 만나게 된다.

You can't specify target table 'revenue' for update in FROM clause

MySQL에는 SELECT 서브쿼리를 실행한 테이블과 동일한 테이블에서 DELETE하지 못하는 제약이 걸려있기 때문에 발생하는 에러이다. 이 에러메시지를 피해가기 위해서 별도로 테이블을 생성해서 중간 데이터를 한번 저장한 후에 DELETE 쿼리만 다시 실행하면 된다. 하지만 단지 DELETE 쿼리 하나를 위해 명시적으로 임시 테이블을 추가로 생성한다는것은 너무 번거로웠다. 그래서 뭔가 더 간단한 방법이 있지 않을까해서 찾아보니 다음과 같이 해결이 가능했다.

2차 시도: 성공

DELETE
FROM revenue 
WHERE id IN 
(
    SELECT id FROM (SELECT id FROM revenue GROUP BY collect_date, currency HAVING count(*) > 1) temp_table
) 

• GROUP BY 쿼리를 이용하여 데이터를 aggregate 한 후에 중복된 데이터가 하나 이상 존재하는 경우를 찾는다.
• 찾아진 데이터의 id 값을 temp_table 이라는 임시테이블로 명명한다.
• temp_table로부터 id 값을 다시 SELECT해서 DELETE한다.

이 방법에서는 SELECT한 데이터를 temp_table이라는 임시테이블로 지정한 후에 temp_table로 부터 SELECT한 id값으로 reveue 테이블에 있는 데이터를 DELETE 했다. 즉, 같은 테이블에 대한 DELETE 작업이 아니기 때문에 제약을 피할 수 있게 된다.