I have a table in which the id is constantly increasing. The Id does not always increase by the same value and it is not an auto_increment due to a api, but it increases permanently. Example:

Now I just want to keep the last X rows, remove rows with lower ids. I was able to find a similar but not a working solution for myself to this problem.

I thought that something like that would work, but there is an error:

DELETE FROM `games` WHERE `matchId` NOT IN(SELECT `matchId` FROM `games` ORDER BY `matchId` DESC LIMIT X)

"#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'"

Im running the mysql version 8.0.27 on a phpmyadmin 5.1.1 web interface.

Is there a way to keep the newest x rows without triggering this error?

CodePudding user response：

WITH cte AS (
 SELECT id, ROW_NUMBER() OVER (ORDER BY id DESC) AS rownum FROM games
)
DELETE games FROM games JOIN cte USING (id) WHERE rownum > 2;

CodePudding user response：

You can use a CTE instead:

with u as
(select Id from table_name order by Id desc limit 5)
delete from table_name
where Id not in(select Id from u)