how to delete the records from a table after a limit.

Question

I have a table like this:

CREATE TABLE vhist ( id int(10) unsigned NOT NULL auto_increment, userId varchar(45) NOT NULL, mktCode int(10) unsigned NOT NULL,
insertDate datetime NOT NULL, default NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

A user can have more than one record. I need an SQL statement which will keep the most recent 50 records and delete any thing after that limit.

I need that in a single sql statement.

I tried this but failed

delete from vhist v where v.id not in (select v.id from vhist v where v.userId=12 order by insertDate desc limit 50)

but this failed on MYSQL saying IN cannot be used with a limit.

Any help?

Answer 1

You need a subquery, like this:

DELETE FROM vhist WHERE id NOT IN (
    SELECT id FROM (
        SELECT id FROM vhist WHERE userId = 12 ORDER BY insertDate DESC LIMIT 50
    ) as foo
);