mysql 중복 데이터 처리 경험 (mysql delete 기능,중복 데이터 조회)

30.08.2021 — MYSQL, 2021 — 3 min read

배경

실제 기부데이터는 1개이고, 기부목록에는 2개로 보이는 문제가 발생했다. 기부 테이블과 기부 영수증은 left 조인을 해서 데이터를 조회합니다. 문제는 기부영수증 데이터가 2개여서 목록에도 2개로 보입니다.

기부영수증 데이터를 삭제하고 , 테이블에 1개만 들어가도록 제약을 설정하기로 했고 수정작업을 위해 데이터를 조회했습니다.

donation_receipts table 을 보니

id 4,6번은 같은 donation_id가 존재한다

id	nationality_type	name	...	donation_id
1	NATIVE	A	...	17
2	NATIVE	B	...	16
3	NATIVE	C	...	14
4	NATIVE	D	...	22
5	NATIVE	D	...	22

(1) 중복된 데이터 찾기

중복된 데이터가 존재하는 기부건 수 확인 쿼리

SELECT * FROM donation_receipts  GROUP BY donation_id HAVING count(*) > 1;

*GROUP BY 절 사용하기, HAVING절 안에 COUNT 함수 사용하기 참조

중복된 기부번호를 갖은 기부영수증 조회 쿼리

select * from donation_receipts
where donation_id in (
    SELECT donation_id FROM donation_receipts  GROUP BY donation_id HAVING count(*) > 1 -- 중복된 기부번호 22
)

(2) 기부영수증 데이터를 삭제 - 데이터 정제 작업

START TRANSACTION;
-- 아래 쿼리로 기부영수증 목록의 donation_id 가 중복인 경우 id 값이 작은 경우를 삭제하기로함
DELETE
-- select *
from donation_receipts
    -- 또 서브쿼라를 사용하는 이유는
    -- Error Code: 1093. You can't specify target table 'donation_receipts' for update in FROM clause	0.000 sec 발생했었음
    -- MySQL에서 UPDATE & DELETE 를 할 때 자기 테이블의 데이터를 바로 사용하지 못하기 때문에 서브쿼리를 하나 더 사용한다.
where id in (
    select k.id from (
        select x.id
        from(select * from donation_receipts where donation_id in ( SELECT donation_id FROM donation_receipts  GROUP BY donation_id HAVING count(*) > 1 )) x
        --중복된 기부번호를 갖은 기부영수증 id 목록에서
        where x.id != (select max(id) from donation_receipts y where y.donation_id=x.donation_id)) k
        -- id 값이 max 가 아닌 경우의 기부영수증 id
);

-- 제대로 삭제되었느지 확인
select *
from donation_receipts
where donation_id in (SELECT donation_id FROM donation_receipts GROUP BY donation_id HAVING count(*) > 1);

commit;
-- rollback;

(3) 기부영수증 테이블의 donation_id 컬럼에 unique 설정

참고

UPDATE & DELETE 를 할 때 자기 테이블의 데이터를 바로 사용하지 못하기 때문에 서브쿼리를 하나 더 사용