MySQL - effectively delete all records except the last N

Question

I started learning SQL recently, and now I was asked to perform a simple delete on a table, saving the last 100 records for each user. I explored the best approach (more efficient way) to archive this and found some possible solutions ( SQL query: delete all records from the table except the last N ? , Delete everything except vertex n from the database table in SQL ), but for me it’s very difficult to choose one based on efficiency. Therefore, I am here to ask for your help.

Here is a table called "access" where we keep access to the user log.

access:
- id (autoincrement) - primary
- userid (integer 11) - key
- refer (varchar 100)
- date (date/time)

My idea is to delete old entries from userid every time the same user enters the system just before inserting a new log.

I tried this code below but got an error: this version of MySQL does not yet support "LIMIT and IN / ALL / ANY / SOME subquery"

DELETE FROM
  access
WHERE
  id NOT IN (
    SELECT id FROM access WHERE userid = 10 ORDER BY id DESC LIMIT 100
  );

Please can you offer me some solution? Thank!

Answer 1

Try DELETE JOIN:

delete a from access a left join (
    select id
    from access
    order by id desc limit 1000
) b on a.id = b.id
where b.id is null;

If you want to save the 1000 best entries of this user (for example, 123) by deleting:

delete a from access a left join (
    select id
    from access
    where userid = 123
    order by id desc limit 1000
) b on a.id = b.id
where b.id is null;

If you want to delete rows only for user 123, except for the top 1000 for this user:

delete a from access a left join (
    select id
    from access
    where userid = 123
    order by id desc limit 1000
) b on a.id = b.id
where b.id is null
and a.userid = 123;

Answer 2

Not an expert in Mysqlnot sure why this is not allowed in Mysql. Try something like this

DELETE a
FROM   access a
       INNER JOIN (SELECT id
                   FROM   access
                   WHERE  userid = 10
                   ORDER  BY id DESC 
                   LIMIT 100) b
               ON a.id <> b.id 

. ,