How to delete the records from a table after a limit

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?

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
);