CODEWITHSUNDEEP
mysql
Markaos
Markaos

Reputation: 659

MySQL set OFFSET depending on subquery


I'm trying to delete all records older than one week while keeping at least one for each user.
Example:

| ID | user |   date   | other columns...
|  1 | 1234 | -2 days  | ...    
|  2 | 1234 | -3 days  | ...
|  3 | 1234 | -8 days  | ...
|  4 | 5678 | -9 days  | ...
|  5 | 5678 | -10 days | ...

Should become

| ID | user |   date   | other columns...
|  1 | 1234 | -2 days  | ...    
|  2 | 1234 | -3 days  | ...
|  4 | 5678 | -9 days  | ... // Keeping the most recent record for this user

So far I've made this, but it uses CASE to set OFFSET, so it doesn't work:

DELETE FROM transactions WHERE ID < (
  SELECT ID FROM (
    SELECT ID FROM transactions t WHERE
    DATE(date) <= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND
    user = transactions.user 
    ORDER BY ID DESC
    LIMIT 1 OFFSET CASE WHEN EXISTS (
      SELECT ID FROM transactions x WHERE
      DATE(date) > DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND
      user = transactions.user
    ) THEN 0 ELSE 1 END
  )
)

So the question is: how to fix the code above?

P.S.: I'm relatively new to anything except most basic operations in SQL

Upvotes: 0

Views: 154

Answers (2)

eggyal
eggyal

Reputation: 125865

By grouping the transactions by user, you can determine those that you wish to preserve:

SELECT   user, MAX(date) date
FROM     transactions
GROUP BY user

You can then make an outer join between these results and your original table using the multiple-table DELETE syntax in order to delete only the desired records:

DELETE transactions
FROM   transactions NATURAL LEFT JOIN (
         SELECT   user, MAX(date) date
         FROM     transactions
         GROUP BY user
       ) t
WHERE  date < CURRENT_DATE - INTERVAL 7 DAY
   AND t.date IS NULL

Upvotes: 2

safin chacko
safin chacko

Reputation: 1390

try

DELETE FROM transactions tt WHERE tt.id NOT IN (
SELECT ID FROM transactions t WHERE
    DATE(t.date) <= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND
    t.user = tt.transactions.user 
    ORDER BY t.ID DESC limit 1
)

Upvotes: 0

Related Questions