MySQL / MariaDB - Remove all but last record per day from database

Question

I'm using MariaDB and would like to purge records periodically from my database leaving only the last record in the DB per day, so over 7 days there would be only 7 records remaining.

I've found something similar to what I want to do here.

DELETE FROM `table` WHERE id NOT IN (   SELECT id   FROM (
  SELECT id
  FROM `table`
  ORDER BY id DESC
  LIMIT 1   ) foo );

This deletes all but the last record but I would like something which will leave the last record each day so I have the latest entry daily. The solution doesn't have to iterate back through the days, I can run this every day at say midnight to process the day before.

I have a column called 'stamp' which is a timestamp column.

Incidentally, for this kind of thing, it's often faster to create a new table retaining just the data you want to keep, and then dropping the old table, and renaming the new one. — Strawberry, May 07 '20 at 15:37
Why -1 on my question? I'm a novice and asked for help. No need. — djmattc, May 11 '20 at 07:54

score 0 · Answer 1 · answered May 24 '20 at 20:56

Much faster if the table is non-trivial size:

CREATE TABLE new LIKE tbl;
INSERT INTO new
    SELECT * FROM tbl WHERE ... ORDER BY ... LIMIT 1;
RENAME TABLE tbl TO old,
             new TO tbl;
DROP TABLE old;

Bonus: tbl is available throughout the process. That is, no downtime.

That and other deletion tips: http://mysql.rjweb.org/doc.php/deletebig

NiteRain · Accepted Answer · 2020-05-16T05:51:20.473

-1

If you have a date/datetime field, then you could do something like this:

DELETE a FROM `table` a INNER JOIN `table` b on (date_format(a.date, '%Y-%m-%d') = date_format(b.date, '%Y-%m-%d') and a.id < b.id)

I am using a myorders table instead of table, but it pretty much looks the same:

edited May 16 '20 at 05:51

answered May 07 '20 at 15:55

NiteRain

663
8
14

instead of using date_format, you can just use date(a.date) and date(b.date) – NiteRain May 08 '20 at 00:13
Sorry, I don't understand this. Why is there a reference to 2 tables? I only have one. – djmattc May 11 '20 at 08:03
This is used to eliminate rows that are less than the biggest item in the same table, note the table that I am using is the same table, I am joining the date to match, but then I am only eliminating the rows that are less than b.id. – NiteRain May 11 '20 at 17:02
If you want to see which rows will get deleted you can just do this change "DELETE a" to "SELECT a.*" this will show you the rows that will be deleted so you can see if it does what you wanted to do on real data or some test data you create. – NiteRain May 11 '20 at 21:26
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table b on (date_format(a.date, '%Y-%m-%d') = date_format('%Y-%m-%d') and a.id <' at line 1 – djmattc May 12 '20 at 11:07
I changed my table name to what you were using in your example, since table is a reserve word I guess I needed to backtick them, for my working example, I used a table called myorders and just changed it to your table name without thinking about needing to add backticks. – NiteRain May 12 '20 at 15:04
I've got it working now, thanks. Your screenshot differs from the code snippet. 'b.date' is missing. – djmattc May 14 '20 at 06:13

score -1 · Answer 3 · answered May 07 '20 at 16:19

-1

Try this once. I have selected all the records from the table having today's date and ordered in descending order and leaving the first one i.e latest one in your case intact and deleting the remaining ones. Also, the other day's records are preserved since only those records are selected which are having today's date. You can modify the date part to include the dates required.

DELETE FROM table WHERE id=(SELECT id FROM table where DATE(tableDate)=CURDATE() ORDER BY id DESC OFFSET 1) AS SUBQUERY

answered May 07 '20 at 16:19

cyperpunk

664
7
13

I'm getting unrecognised statement for "AS". – djmattc May 11 '20 at 07:58
@djmattc Try this then `DELETE FROM table WHERE id IN (SELECT id FROM table where DATE(tableDate)=CURDATE() ORDER BY id DESC OFFSET 1)` – cyperpunk May 11 '20 at 12:41
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OFFSET 1)' at line 1 – djmattc May 12 '20 at 11:06

MySQL / MariaDB - Remove all but last record per day from database

3 Answers3

Linked