0

Basically, I have a table where I store messages for each user. Each user is allocated 1,000 messages, by 30 days.

id user_id message created_at
1 12 ... 2020-01-01 16:30:45
2 45 ... 2020-01-02 16:31:12
3 12 ... 2020-01-02 16:61:15

My intention is to run a single SQL statement to delete the exceeding messages of each user by quantity, and those older than 30 days.

The only solution I have so far is to get the user_id using a GROUP BY clause, and iterate over each with a subquery.

DarkGhostHunter
  • 153
  • 1
  • 7
  • 1
    I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using. –  Aug 19 '21 at 05:13
  • 2
    If the number of messages for one user exceeds 1000 but all are younger than 30 days, do you still want to delete the excess messages? Do you want to delete the oldest excess message or the newest? –  Aug 19 '21 at 06:56
  • Please provide a fiddle (dbfiddle.uk) with your table structures and (sample) data along with your desired result. Then copy that back here and also include the link to the fiddle - help us to help you! – Vérace Aug 19 '21 at 07:08
  • have you mysql 8? – nbk Aug 19 '21 at 07:52

1 Answers1

1

You can use ROW_NUMBER in a self-joined DELETE statement:

DELETE t
FROM YourTable t
JOIN (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY t.created_at DESC) AS rn
  FROM YourTable t
) t2 ON t2.id = t.id
WHERE t2.rn > 1000 OR t2.created_at < DATE_SUB(CURDATE(), INTERVAL 30 DAY);

This works for MySQL.

In SQL Server you can modify the derived table directly

DELETE t
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY t.created_at DESC) AS rn
  FROM YourTable t
) t
WHERE t.rn > 1000 OR t.created_at < DATE_ADD(day, -30, GETDATE());
Charlieface
  • 12,780
  • 13
  • 35