I have a table like this:

Schema (MySQL v5.7)

CREATE TABLE likethis
    (`id` int, `userid` int, `date` DATE)
;
    
INSERT INTO likethis
    (`id`, `userid`, `date`)
VALUES
        (1, 1, "2021-11-15"),
        (2, 2, "2021-11-15"),
        (3, 1, "2021-11-13"),
        (4, 3, "2021-10-13"),
        (5, 3, "2021-09-13"),
        (6, 2, "2021-09-13");

View on DB Fiddle

I want to delete all records which are older than 14 days, EXCEPT if the user only has records which are older - than keep the "newest" (biggest "id") row for this user.

Desired target after that action shall be:

i.e.: User ID 1 only has records within the last 14 days: Keep all of them. User ID has a record within the last 14 days, so delete ALL his records which are older than 14 days. User ID 3 has only "old" records, i.e. older than 14 days - so keep only the one newest of those records, even though it's older than 14 days.

I thought of something like a self join with a subquery where I group by user-id ... but can't really get to it ...

CodePudding user response：

This query could work

DELETE b
FROM likethis a
JOIN likethis b ON a.`userid` = b.`userid` AND a.`date` > b.`date`
WHERE b.`date` < NOW() - INTERVAL 14 DAY

CodePudding user response：

I believe you can use case function in MySql

For Example -

SELECT TableID, TableCol,
CASE
    WHEN Date > 30 THEN "Delete statement"
    ELSE "Dont Delete (Record is not 30"
END
FROM TableName;

Suggested link:

1. https://www.w3schools.com/sql/func_mysql_case.asp
2. https://dev.mysql.com/doc/refman/5.7/en/case.html

Hope this helps...