CODEWITHSUNDEEP
phpmysql
user2372936
user2372936

Reputation: 79

How to delete all rows from a table except newest 10 rows

I have a Mysql schema of result (str), created (timestamp, current on create) and I would like to run a query to delete all except the last 10, how would I do that?

Upvotes: 0

Views: 296

Answers (4)

Strawberry
Strawberry

Reputation: 33935

OK, this is 9 rows, not 10, but you get the idea...

SELECT * FROM created;
+---------------------+
| t                   |
+---------------------+
| 2013-05-28 19:17:03 |
| 2013-05-28 19:17:06 |
| 2013-05-28 19:17:09 |
| 2013-05-28 19:17:11 |
| 2013-05-28 19:17:13 |
| 2013-05-28 19:17:16 |
| 2013-05-28 19:17:17 |
| 2013-05-28 19:17:19 |
| 2013-05-28 19:17:21 |
| 2013-05-28 19:17:23 |
| 2013-05-28 19:17:25 |
| 2013-05-28 19:17:27 |
| 2013-05-28 19:17:29 |
| 2013-05-28 19:17:31 |
| 2013-05-28 19:17:33 |
| 2013-05-28 19:17:35 |
| 2013-05-28 19:17:37 |
| 2013-05-28 19:17:39 |
+---------------------+
18 rows in set (0.00 sec)

DELETE a
  FROM created a
  JOIN
     (  SELECT x.*
          FROM created x
          JOIN created y
            ON y.t >= x.t
         GROUP
            BY x.t
        HAVING COUNT(*) = 10
     ) b
    ON b.t >= a.t;

SELECT * FROM created;
+---------------------+
| t                   |
+---------------------+
| 2013-05-28 19:17:23 |
| 2013-05-28 19:17:25 |
| 2013-05-28 19:17:27 |
| 2013-05-28 19:17:29 |
| 2013-05-28 19:17:31 |
| 2013-05-28 19:17:33 |
| 2013-05-28 19:17:35 |
| 2013-05-28 19:17:37 |
| 2013-05-28 19:17:39 |
+---------------------+

Upvotes: 0

iamyoufromthefuture
iamyoufromthefuture

Reputation: 1

You can also use a variant of a method for finding the Nth maximum value in a table using a self join.

DELETE FROM tablename WHERE created < (
SELECT created FROM tablename t1 WHERE
 (N - 1) = (SELECT COUNT( DISTINCT( created )
            FROM tablename t1
            WHERE t2.created > t1.created ) ) )

The interior part of the query gives you the Nth largest value of "created" from your table, which you can use as a reference to delete everything that came earlier.

Upvotes: 0

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

You should collect the 10 ids first

CREATE TEMPORARY TABLE ids_to_keep ENGINE=MyISAM
SELECT id FROM tablename WHERE 1=2;
INSERT INTO ids_to_keep
SELECT id FROM tablename ORDER BY created DESC LIMIT 10
ALTER TABLE ids_to_keep ADD PRIMARY KEYS (id);

From there, try one of these two techniques:

TECHNIQUE #1 : DELETE JOIN

DELETE A.* FROM tablename A
LEFT JOIN ids_to_keep B USING (id)
WHERE B.id IS NULL;
DROP TABLE ids_to_keep;

TECHNIQUE #2 : TABLE RECREATION

CREATE TABLE tablename_new LIKE tablename;
ALTER TABLE tablename RENAME tablename_org;
INSERT INTO tablename_new
SELECT A.* FROM tablename_org A INNER JOIN ids_to_keep B USING (id);
DROP TABLE tablename_org;
ALTER TABLE tablename_new RENAME tablename;
DROP TABLE ids_to_keep;

Give it a Try !!!

Upvotes: 0

Orangepill
Orangepill

Reputation: 24645

  DELETE FROM tablename 
    WHERE id NOT IN (
      SELECT id FROM tablename 
      ORDER BY created DESC 
      LIMIT 10);

Upvotes: 8

Related Questions