MySQL deletes all subquery results

Question

Ok, so if you need a background, look at my previous question

Finding out which of my records is not a duplicate is pretty simple:

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 decided to move them to another table called "no_duplicates", and then delete them from the original table. Then I could see duplicates in a single table, correct them and add no_dupes back. But for now:

 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 causes an error:

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

I assume that although the query returns unique records already in the table, deletion using an aggregate function is not kosher. This is understandable, except that I do not know what else I can do to delete only the records that I deleted. I searched and did not find the syntax "After INSERT kills the records in the source table", and I assume that it will fail anyway, for the same reason as deleting.

So can someone help me find the missing part?

Answer 1

First, you do not need ORDER BY on INSERT and DELETE. This is just useful if you need them for a presentation that is not relevant to these operations.

If your EVENTLOG table has a primary key (for example, ID), you can generate your DELETE statement as follows:

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

Answer 2

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

Why not try something like this:

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

Answer 3

What database? If MySQL then

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

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

Answer 4

You should take a look at the DELETE syntax; there is neither GROUP BY, nor HAVING, nor ORDERs.

Answer 5

You can try to add a primary key auto-increment column, move your unique code to a new table, and delete the event log. * from eventlog e internal join no_dupes nd on e.id = nd.id