MySQL - Efficiently delete all records except last N
|Added at||2017-01-05 04:01|
I started learning SQL recently and now was asked to perform a simple deletion on a table, keeping the last 100 records for each user. I was researching the best approach (more efficient way) to archive this, and found some possible solutions (SQL query: Delete all records from the table except latest N? , Delete all but top n from database table in SQL), but it is kind intricate for me to choose one based on efficiency. So I am here to ask for your help.
Here is the table called "access" where we keep users access log.
My idea is to delete old records from the userid everytime this same user enter the system, just before inserting the new log.
I have tried this code bellow but got error: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
Please, can you suggest me some solution? Thanks!