MySQL delete all results of sub-query

Question

Tonight may be the night I crack...

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

I have finally decided that if this is possible, It's not worth the time it will take to figure out, since I really should have had this done before tonight anyway. So I decided to go another route:

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 if anyone out there can please help me find the missing piece, I'll be really, really grateful.

---

Real quick: Thank your for everyone so far. You've all been really insightful and educational. I just want to explain why I'm a bit frantic in case I lack my usual cool and curious attitude.

Essentially this is a project I was working on more for fun and to show off around the office. I was asked a few days ago if it was ready to go live when it hadn't really been tested outside of small group. Next thing I know, it's live and held together with twine and bubble gum. Now I'm trying to make it actually ready for the next go around (bi-monthly reports). Meanwhile, it was already used in the real-world, so there is some real-world data that if I screw this up just a tiny bit more, will be gone and can't be retrieved without doing it all from hand.

So! There are no primary keys, I'm embarrassed to say, but there are about 3 copies of everything BUT the real-world data.

I guess you're all caught up. AGain, thank you for any help you can give.

Answer 1

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 2

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
)

Answer 3

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

Answer 4

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 5

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 )