[MySQL] DELETE & UPDATE

MySQL은 데이터의 무결성과 잠재적인 충돌을 방지하기 위해 UPDATE나 DELETE 작업을 수행할 때, 해당 테이블에 잠금을 설정한다.

 

<오류가 발생하는 경우>

delete from person
where id not in (
    select min(id)
    from person
    group by email
)

 

 

<CTE로 해결하는 방법>

with cte as(
    select min(id) id
    from person
    group by email
)
delete from person
where id not in (select id from cte)

 

<JOIN으로 해결하는 방법>

DELETE p1
FROM person p1
LEFT JOIN (
    SELECT MIN(id) AS min_id
    FROM person
    GROUP BY email
) p2 ON p1.id = p2.min_id
WHERE p2.min_id IS NULL;