MySQL delete all results of sub-query

Question

Okay, so if you would like a back story, look at my previous question

Figuring out which of my records is not a duplicate is quite easy:

SELECT *
FROM eventlog
GROUP BY event_date, user
HAVING COUNT(*) = 1
ORDER BY event_date, user

This returns all of my non-duplicates. So I thought I'd move them over to another table called "no_duplicates" and then delete them from the original table. Then I could see the duplicates all alone in the original table, fix them up, and add the no_dupes back. But while:

INSERT INTO no_duplicates
SELECT *
FROM eventlog
GROUP BY event_date, user
HAVING COUNT(*) = 1
ORDER BY event_date, user

Works like a charm, the following throws an error:

DELETE
FROM eventlog
GROUP BY event_date, user
HAVING COUNT(*) = 1
ORDER BY event_date, user

My guess is that while the query returns unique, already-in-the-table records, deleting by a aggregate function isn't kosher. Which is understandable except I don't know what else I can do to secure that only the records I moved are deleted. I searched and found no "After INSERT kill the records in the original table" syntax, and my guess is that it would fail anyway, for the same reason the delete failed.

So, can someone help me find the missing piece?

Answer 1

Firstly, you do not need the ORDER BY on the INSERT and DELETE. It's just useful if you need them for presentation which is not the case with those operations.

If your EVENTLOG table has a primary key (like ID) you could form your DELETE-statement like this:

DELETE 
FROM eventlog
WHERE id IN (
  SELECT *
  FROM eventlog
  GROUP BY event_date, user
  HAVING COUNT(*) = 1
)