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

I would not delete with the aggregate function. I think you would like to delete all the inserted data ?

Why not try something like this:

DELETE 
FROM eventlog
WHERE (user, event_date) IN (SELECT user, event_data FROM no_duplicates)

Answer 2

You may try adding a primary key auto increment column, move your uniques into a new table and do a delete eventlog.* from eventlog e inner join no_dupes nd on e.id=nd.id

Answer 3

What's the database? If MySQL then

Currently, you cannot delete from a table and select from the same table in a subquery.

At least for 5.0 version. ( http://dev.mysql.com/doc/refman/5.0/en/delete.html )

Answer 4

You should have a look at the DELETE syntax, there are neither GROUP BYs, nor HAVINGs, nor ORDERs.

Answer 5

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
)