CODEWITHSUNDEEP
mysqlsqlsql-delete
Paul Schorey
Paul Schorey

Reputation: 1182

SQL delete rows after a select query with a limit

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?

Upvotes: 1

Views: 4179

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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).

Upvotes: 0

MikeS
MikeS

Reputation: 1764

Try:

DELETE FROM tests 
WHERE tests.user_id = 38
and tests.ID in (SELECT id 
FROM tests t
WHERE t.user_id = tests.user_id
ORDER BY test_timestamp DESC LIMIT 20, 100);

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

For attempt 1, this is the correct syntax

delete from tests
where the primary key field in 
(select the primary key field 
etc
)

Upvotes: 0

Related Questions