0

I want to delete records from my table when an end-date column is over 3 years expired past the current date

See below for what i have already tried

DELETE FROM membership
WHERE (SELECT EXTRACT (YEAR FROM end_date)) <
(SELECT EXTRACT (YEAR FROM (SELECT DATE_ADD( CURDATE(), INTERVAL -4 YEAR))))

I expect results that are from 2016 and before that to be deleted in my table

Thanks in advance

xQbert
  • 34,733
  • 2
  • 41
  • 62
Gary Mannion
  • 29
  • 7
  • `WHERE End_Date < Date_Add(curdate(),Interval -4 Year)` Delete all records 4 years prior to today. – xQbert Apr 05 '19 at 16:23

2 Answers2

2

This will delete everything that has an end_date with a year of 2016 or older:

DELETE FROM membership WHERE YEAR(end_date) <= YEAR(CURDATE() - INTERVAL 3 YEAR);

EDIT: If you want to delete everything with an end_date that is over 3 years old:

DELETE FROM membership WHERE end_date < NOW() - INTERVAL 3 YEAR;
derek.wolfe
  • 1,086
  • 6
  • 11
  • @Gary Mannion: Glad to help! – derek.wolfe Apr 05 '19 at 16:43
  • Just a side note; if there is **a lot** of data, and the year is what matters; it would probably better to use `end_date <= (calculated last day or datetime of desired year)`. If you have an index on end_date, YEAR(end_date) would prevent MySQL from taking advantage of it. – Uueerdo Apr 05 '19 at 17:04
  • I did not think of that. That could be easily acheived by doing this: `DELETE FROM membership WHERE end_date < CONCAT(YEAR(CURDATE() - INTERVAL 2 YEAR), '-01-01');` – derek.wolfe Apr 05 '19 at 17:21
0

Some notes:

  • compare the bare column end_date against a value, as a date type
  • test expressions in SELECT statements, before running DELETE

What is the first date value that is greater than the end_date on the rows you want to delete? Test that expression in a SELECT statement.

SELECT DATE_FORMAT( NOW(), '%Y-01-01') + INTERVAL -3 YEAR

returns

2016-01-01

or, date value four years before now 

SELECT DATE(NOW()) + INTERVAL -4 YEAR

returns: 

2015-04-05

adjust that expression until it returns the value we need. Then we can include that expression in a statement, comparing to bare column end_date column, like this:

SELECT m.* 
  FROM membership m 
 WHERE m.end_date < DATE(NOW()) + INTERVAL -4 YEAR
 ORDER BY m.end_date DESC

or if we know that the date value we need is '2015-04-05' or '2017-01-01' then we can just specify that as a literal:

SELECT m.* 
  FROM membership m 
 WHERE m.end_date < '2017-01-01' + INTERVAL 0 MONTH
 ORDER BY m.end_date DESC

After we confirm that the SELECT statement is returning the set of rows we want to delete, then we can replace the SELECT keyword with DELETE.

spencer7593
  • 106,611
  • 15
  • 112
  • 140