SQL delete rows after a select query with a limit

Question

I would like to delete all but the user's most recent 20 tests from a table that has multiple users in it.

attempt 1:

DELETE FROM tests WHERE EXISTS (SELECT * FROM tests WHERE user_id = 38 ORDER BY test_timestamp DESC LIMIT 20, 100);

attempt 2:

DELETE FROM tests WHERE user_id = 38 ORDER BY test_timestamp DESC LIMIT 20, 100;

Are there any changes that I can make either of the above samples to work or do I need to use PHP to setup a rowcount variable to work around my offset issue?

Answer 1

In MySQL, you can do this using a join:

DELETE t
    FROM tests t JOIN
         (SELECT tt.timestamp
          FROM tests tt
          WHERE t.user_id = 38
          ORDER BY tt.timestamp DESC
          OFFSET 19 LIMIT 1
         ) tt
         ON t.user_id = tt.user_id and t.timestamp < tt.timestamp;

The two more "obvious" methods are not supported by MySQL.

• MySQL does not support OFFSET in the LIMIT clause for DELETE.
• MySQL does not support operations in the WHERE clause on the table being modified (without adding additional complication to the query).