CODEWITHSUNDEEP
phpmysql
deerox
deerox

Reputation: 1055

Deleting all but the last 30 rows in database table with MySQL

I have simple question. How can I delete all but the last 30 rows in a database table? For example, take the following query:

DELETE FROM Comments
WHERE got='$user_id'

What else do I need to do in order to keep the last 30 rows?

This is what the Comments table looks like:

enter image description here

Upvotes: 2

Views: 2620

Answers (3)

David Jones
David Jones

Reputation: 10219

If your version if MySQL supports limits within sub-queries, then you can use a sub-query that selects the 30 most recent rows. Then, you can delete everything except those rows which were found in the sub-query.

DELETE
FROM Comments
WHERE got='$user_id'
  AND got NOT IN
    (SELECT got
     FROM Comments
     ORDER BY TIMESTAMP DESC LIMIT 30)

If your MySQL version does not support limits within sub-queries, then you'll need to split it up into two separate queries:

QUERY 1

SELECT got FROM Comments WHERE got='$user_id' ORDER BY TIMESTAMP DESC LIMIT 30

The results of query 1 should be stored in a single string variable (using the format 1,2,3...) and passed into query 2. (The explode and implode functions may come in handy when formatting the string.)

QUERY 2

DELETE
FROM Comments
WHERE got='$user_id'
  AND got NOT IN ($formatted_result_from_query_1)

Upvotes: 4

c 2
c 2

Reputation: 1167

Store the last 30 rows in a temporary table and then exclude those rows from your delete:

CREATE TEMPORARY TABLE tmp AS (SELECT id FROM Comments WHERE got="$user_id" ORDER BY timestamp DESC LIMIT 5);

DELETE FROM Comments
WHERE got="$user_id" AND NOT EXISTS (SELECT id FROM tmp)

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

You can use LIMIT to do so:

DELETE c1
FROM Comments AS c1
LEFT JOIN 
(
   SELECT id 
   FROM comments
   WHERE got = ...
   ORDER BY id DESC
   LIMIT 30
) AS c2  
WHERE c2.id IS NULL;

See it in action here:

  • SQL Fiddle Demo (I just tried it with 5 rows only, with a simplified schema for your table)

Upvotes: 1

Related Questions