0

I have a table structured this way: 

id, event_id, event_date
1, 12345, 2016-02-03
2, 12345, 2016-02-03
3, 12345, 2016-02-06
4, 12345, 2016-02-06
5, 12345, 2016-02-06
6, 12342, 2016-02-03
7, 12342, 2016-02-03
8, 12342, 2016-02-03

Notice that the id is unique, but not the event_id and the event_date (which is quite different from the claimed duplicate).

Is there a way to shorten this SQL code:

First Step: get event id

SELECT DISTINCT event_id FROM events

Second Step: get event_date using id

SELECT DISTINCT event_date
        FROM events
        WHERE event_id='<event_id>'

Third Step: get event_id, event_date, and event_count

SELECT id, event_id, event_date, COUNT(*) as event_count 
        FROM events
        WHERE event_id ='<event_id>'
        AND event_date='<event_date>'

Forth Step: delete record if event_id and event_date are the same.

DELETE FROM events
        WHERE event_id ='<event_id>'
        AND event_date='<event_date>'
        LIMIT <event_count-1>;

I put all of them in functions, and it pulls up all the data slow from MySQL database because of multiple queries and huge data, but is there a way to query once?

Franz Noel
  • 1,820
  • 2
  • 23
  • 50
  • Are you just trying to get rid of all the rows with duplicate `event_id` and `event_date`? – Barmar Mar 30 '16 at 22:12
  • Yes, I am trying to get rid of all the rows with duplicate event_id and event_date... Looking for the simplest query. However, the event_id is not the unique key, but id is. – Franz Noel Mar 31 '16 at 00:14
  • I hope you found the answers at the question I linked to helpful. – Barmar Mar 31 '16 at 00:22
  • The link you gave me indicates that you have id as unique. However, my events table has not been normalized correctly, and the event_id (not necessarily id) and the event_date can appear multiple times at the same time. Keeping the highest id and deleting the rest may not be the solution because deleting the id will delete either all event_id or all event_date after deleting the highest id. (I've tried the highest id solution before). Also, in the link there is only one DISTINCT. This problem has two DISTINCT. – Franz Noel Mar 31 '16 at 00:29
  • It doesn't delete the highest, it keeps the highest one and deletes all the rest. So there won't be any duplicates. – Barmar Mar 31 '16 at 00:31
  • If there are multiple columns that need to be distinct, just compare them all in the `WHERE` clause. `WHERE e1.event_id = e2.event_id AND e1.event_date = e2.event_date AND e1.id < e2.id` – Barmar Mar 31 '16 at 00:32
  • If I'm going to put this in a `WHERE` clause, then there will still be `three queries` to get those two `DISTINCT` first before executing the `DELETE` query. I was kinda looking for a one shot query. Otherwise, I could have just accepted my code. – Franz Noel Mar 31 '16 at 00:42
  • It **is** a 1-shot query. Did you read the first answer in the question I linked to? – Barmar Mar 31 '16 at 00:43
  • 1
    `DELETE FROM events e1, events e2 WHERE WHERE e1.event_id = e2.event_id AND e1.event_date = e2.event_date AND e1.id < e2.id` This is just like the answer in that question, except with your table and column names replacing the ones in that question. – Barmar Mar 31 '16 at 00:45

0 Answers0