CODEWITHSUNDEEP
mysql
Neil Aronson
Neil Aronson

Reputation: 99

Deleting all but the most recent record in same mySQL table

I have a data table like so

+------------+-------------+---------------+---------------------+
| date       | facebook_id | FB_page_likes | asof                |
+------------+-------------+---------------+---------------------+
| 2016-01-15 | person1     |        119339 | 2016-01-15 11:22:20 |
| 2016-01-15 | person1     |        119340 | 2016-01-15 11:34:00 |
| 2016-01-15 | person2     |         52147 | 2016-01-15 11:22:20 |
| 2016-01-15 | person2     |         52147 | 2016-01-15 11:34:00 |

I have a Python script that reads in the FB_page_likes value automatically and then timestamps it in the asof column. Sometimes the script may run more than once per day, but I only want to keep the most recent values for that day. I'm trying to figure out how to keep only the most recent records for each facebook_id for a given day.

I've tried a subquery: 

delete from dailydata 
where date='2016-01-15' and asof != (select max(asof) from dailydata);

which gave me the error

ERROR 1093 (HY000): You can't specify target table 'dailydata' for update in FROM clause

and then did some research here and found a couple of other questions from people who had this error (MySQL Error 1093 - Can't specify target table for update in FROM clause and You can't specify target table for update in FROM clause. They suggested using "as" to avoid the problem, so I tried this:

delete from dailydata 
where asof not in (
    select max(asof) from (
        select * from dailydata
    ) as temp 
    where date='2016-01-15' group by temp.facebook_id
);

but I'm still getting the same error. Does anyone have any ideas about what I'm missing?

Upvotes: 1

Views: 38

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

I would suggest using a join for this purpose:

delete d
    from dailydata join
         (select date, max(asof) as maxasof
          from dailydata
          where date = '2016-01-15'
          group by date
         ) dd
         on d.date = dd.date and d.asof < d.maxasof;

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

Try this:

delete from dailydata 
where date='2016-01-15' 
and asof not in (select * from (select max(asof) from dailydata) as t);

Upvotes: 1

Related Questions