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
);
精彩评论