메뉴 건너뛰기

조회 수 1948 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄

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 작업이 아니기 때문에 제약을 피할 수 있게 된다.


List of Articles
번호 제목 날짜 조회 수
86 [MySQL] 백업 및 복구 2017.04.12 5391
85 [mysql,mariaDB] 컬럼 암호화, 복호화 2020.11.25 766
84 UPDATE ~ CASE 문을 사용한 조건별 업데이트 file 2020.07.28 1387
83 Union 사용법. 여러 테이블의 같은 값을 한번 쿼리로 추출하기 2017.11.21 3918
82 SQL 고급 file 2017.12.22 6033
81 order by field 와 union, 원하는 대로 정렬 하기 2021.03.26 768
80 no exists, not in 을 이용한 조건에 만족하지 않는 것들 구하기, 둘의 차이점 2021.03.26 125
79 MySQL의 alter table 명령어 모음 2017.04.12 5105
78 MySQL에서 원격 IP 접속 허용하기 2017.04.12 5621
77 MySQL에서 사용자 권한 주기 (grant 명령어) 2017.04.12 5885
76 MySQL〃오라클의 nextval을 MySQL에서 사용하기 2018.07.24 3863
75 mysqlbinlog (mysql 백업, 로그남기기) 2021.03.26 196
74 MySQL/MariaDB 백업 & 복원 - mysqldump 2015.08.07 7384
73 mysql-bin(binary log)파일 정리 및 삭제 2023.01.12 114
72 mysql-bin 로그를 SQL 문으로 변환한는 방법 2018.07.18 1672
71 mysql(union,join) file 2017.11.16 3837
70 mysql(exists, not exists) file 2017.11.16 4088
69 MYSQL 프로시져 PROCEDURE file 2021.03.27 197
68 mysql 포트 바꾸기, 외부에서 접속하기 2021.03.26 5070
67 MySQL 파티션 개요 file 2017.12.22 4150
Board Pagination Prev 1 2 3 4 5 6 7 Next
/ 7

하단 정보를 입력할 수 있습니다

© k2s0o1d4e0s2i1g5n. All Rights Reserved