CODEWITHSUNDEEP
mysqlsqlsql-deletemysql-8.0
Patrick
Patrick

Reputation: 695

Delete all items in a database except the last date

I have a MySQL table that looks (very simplified) like this:

CREATE TABLE `logging` (
  `id` bigint(20) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `level` smallint(3) NOT NULL,
  `message` longtext CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci NOT NULL
);

I would like to delete all rows of a specific level, except the last one (time is most recent).

Is there a way to select all rows with level set to a specific value and then delete all rows except the latest one in one single SQL query? How would I start solving this problem?

(As I said, this is a very simplified table, so please don't try to discuss possible design problems of this table. I removed some columns. It is designed per PSR-3 logging standard and I don't think there is an easy way to change that. What I want to solve is how I can select from a table and then delete all but some rows of the same table. I have only intermediate knowledge of MySQL.)

Thank you for pushing me in the right direction :)

Edit:

The Database version is /usr/sbin/mysqld Ver 8.0.18-0ubuntu0.19.10.1 for Linux on x86_64 ((Ubuntu))

Upvotes: 2

Views: 1730

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65208

You can use ROW_NUMBER() analytic function ( as using DB version 8+ ) :

DELETE lg FROM `logging` AS lg
 WHERE lg.`id` IN
      ( SELECT t.`id` 
          FROM
         (
          SELECT t.*,
                 ROW_NUMBER() OVER (ORDER BY `time` DESC) as rn
            FROM `logging` t
       --  WHERE `level` = @lvl -- optionally add this line to restrict for a spesific value of `level`
         ) t
         WHERE t.rn > 1
       )

to delete all of the rows except the last inserted one(considering id is your primary key column).

Upvotes: 2

Paul Spiegel
Paul Spiegel

Reputation: 31772

If you have an AUTO_INCREMENT id column - I would use it to determine the most recent entry. Here is one way doing that:

delete l
from (
  select l1.level, max(id) as id
  from logging l1
  where l1.level = @level
) m
join logging l
  on  l.level = m.level
  and l.id    < m.id

An index on (level) should give you good performance and will support the MAX() subquery as well as the JOIN.

View on DB Fiddle

If you really need to use the time column, you can modify the query as follows:

delete l
from (
  select l1.level, l1.id
  from logging l1
  where l1.level = @level
  order by l1.time desc, l1.id desc
  limit 1
) m
join logging l
  on  l.level = m.level
  and l.id   <> m.id

View on DB Fiddle

Here you would want to have an index on (level, time).

Upvotes: 0

Parsa Mousavi
Parsa Mousavi

Reputation: 1182

You can do this:

SELECT COUNT(time) FROM logging WHERE level=some_level INTO @TIME_COUNT;
SET @TIME_COUNT = @TIME_COUNT-1;
PREPARE STMT FROM 'DELETE FROM logging WHERE level=some_level ORDER BY time ASC LIMIT ?;';
EXECUTE STMT USING @TIME_COUNT;

Upvotes: 0

Related Questions