mariadb 중복 데이터 1건만 남기고 삭제

작업 전 백업 및 확인 필수 

 

1. 고유키 사용

중복 데이터 조회

select title, start_date, end_date, count(*)  
  from test_table 
 group by title, start_date, end_date
having count(*) > 1;

5,6,7 // 8,9 중복

delete a from test_table a, test_table b 
where a.title = b.title
  and a.start_date = b.start_date
  and a.end_date = b.end_date
  and a.id < b.id; -- 마지막 데이터 남기기
  and a.id > b.id; -- 최초 데이터 남기기

마지막 데이터

최초 데이터

 

2. row_number로 처리

partition by -> 그룹

order by -> 정렬

rownum 1건만 남기기(b 부분) - 삭제 대상

delete a from test_table a,
  (select * 
     from (
         select id, title, start_date, end_date
              , ROW_NUMBER() OVER (PARTITION BY title, start_date ORDER BY end_date desc) AS row_num
         FROM test_table
        ) z 
    where z.row_num > 1) b
 where a.title = b.title
   and a.start_date = b.start_date
   and a.end_date = b.end_date

고유키 있는 경우

delete from test_table where id in
  (select id 
     from (
         select id
              , ROW_NUMBER() OVER (PARTITION BY title, start_date ORDER BY end_date desc) AS row_num
         FROM test_table
        ) z 
    where z.row_num > 1)

 

 

3. max, min을 사용

마지막 데이터를 제외하고 삭제 예제

원본

delete from test_table where id not in(select max(id) from test_table group by title, start_date)

삭제 이후

 

 

4. 아래와 같은 동일한 데이터만 있는 경우

임시 테이블에 Distinct 한 데이터를 넣고 삭제 후 임시 테이블 데이터 이동