DELETE ALL entries, but not the last 10 recent entries.

[jasonc]

i wish to delete all but the 10 most recent entries that fit a certain criteria.

 

i have tried this but does not work, how would i correctly remove all but the most recent ones?

 

DELETE FROM 
    `table` 
  WHERE 
    `user` = 'username' 
  ORDER BY 
  `date` DESC
  LIMIT 10, 1000000

[Ken2k7]

You have an error in your delete query. Please RTM and don't apply things blindly - http://dev.mysql.com/doc/refman/5.0/en/delete.html

[jasonc]

You have an error in your delete query. Please RTM and don't apply things blindly - http://dev.mysql.com/doc/refman/5.0/en/delete.html

 

Thank you Ken, I have tried the query drectly in mysql query window and it gives me the following eror but I do not know how to fix this so it removes all but the most recent 10 entries.

 

i was able to select them ok but not delete them.

using the follwing query.

SELECT * 
FROM

(
  SELECT * 
  FROM 
    `table` 
  WHERE 
    `user` = 'username' 
  ORDER BY 
  `date` DESC
  LIMIT 5, 1000000
) as DT
ORDER BY 
  `date`

 

I did take a look at the link you gave and this is a page I have previously looked at but I can not see how to delete al but the most recent entries method.