How do I delete the duplicates this complex query selects?

Question

I would like to delete the entire row for ALL of the duplicates this query finds. I do not wish to leave a single record. How do I add the delete row syntax to this complicated query?

SELECT u.id, u.School, u.Mascot, u.City, u.State
FROM TABLE u
INNER JOIN
(
    SELECT Mascot, City, State, COUNT(*)
    FROM tablename
    GROUP BY Mascot, City, State
    HAVING COUNT(*) > 1
) temp
    ON temp.Mascot = u.Mascot AND
       temp.City = u.City AND
       temp.State = u.State
ORDER BY
    Mascot, City, State;

see this : https://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql — Ali, Feb 11 '18 at 08:44
This does not answer how to delete All/Both duplicates. I do not want to leave a record. — Wyatt Jackson, Feb 11 '18 at 08:48

Tim Biegeleisen · Answer 1 · 2018-02-11T08:56:00.490

Try grouping and then retaining only those groups having a count of 1:

SELECT
    u.id, u.School, u.Mascot, u.City, u.State
FROM TABLE u
INNER JOIN
(
    SELECT Mascot, City, State, COUNT(*)
    FROM tablename
    GROUP BY Mascot, City, State
    HAVING COUNT(*) > 1
) temp
    ON temp.Mascot = u.Mascot AND
       temp.City   = u.City   AND
       temp.State  = u.State
GROUP BY
    u.id, u.School, u.Mascot, u.City, u.State
HAVING
    COUNT(*) = 1
ORDER BY
    Mascot, City, State;

How do I delete the duplicates this complex query selects?

1 Answers1