Delete N number of old records from table in mysql
I have a LoginTime table like this:
id | user_id | datetime
1 | 1 | 2011-01-19 18:51:01
2 | 1 | 2011-01-19 18:51:02
3 | 1 | 2011-0开发者_开发知识库1-19 18:51:03
4 | 1 | 2011-01-19 18:51:04
5 | 1 | 2011-01-19 18:51:05
6 | 1 | 2011-01-19 18:51:06
7 | 1 | 2011-01-19 18:51:07
8 | 1 | 2011-01-19 18:51:08
9 | 1 | 2011-01-19 18:51:09
10 | 2 | 2011-01-19 18:51:10
I want to keep only 5 latest(by 'datetime' column) records and delete all previous records where user_id=1
Is it possible to achieve this with one mysql query ?
DELETE
FROM LoginTime
WHERE user_id = 1
ORDER BY datetime ASC
LIMIT 5
I believe this will work...
DELETE FROM LoginTime WHERE id IN (
SELECT id
WHERE user_id = 1
ORDER BY datetime DESC
LIMIT 0, 5
)
delete LoginTime
from
LoginTime
left join
(
select id
from LoginTime
where user_id=1
order by `datetime` desc
limit 5
) as not_to_delete
on LoginTime.id=not_to_delete.id
where
not_to_delete.id is null;
PS: please don't use CamelCase for table name, and avoid using reserved keywords for the column name
delete LoginTime
from
LoginTime
left join
(
select id
from LoginTime
where user_id=1
order by datetime desc
limit 5
) as not_to_delete
on LoginTime.id=not_to_delete.id
left join
(
select id
from LoginTime
where user_id=1
) as existance
on LoginTime.id=existance.id
where
not_to_delete.id is null and existance.id is not null;
精彩评论