We help IT Professionals succeed at work.

Delete Range of dates in MySQL

weklica
weklica asked
on
371 Views
Last Modified: 2014-01-06
I have searched the internet and copied in at least 7 solutions that are advertised around, but I simply cannot get it to work.  

Problem:  I have 100,000 rows in a database - all with a date field populated.  I want to delete all rows if the date is older than 14 days.  Among many attempts, this was my last, but the SQL is bad.  Any thoughts for a legit solution that actually works :)

DELETE * FROM hours WHERE ADJAPPLYDTM < DATE_SUB( 2013-12-30, INTERVAL 14 DAY )

I would paste all the others I tried, but it wouldn't do any good at this point since they didn't work and I don't remember them.
Comment
Watch Question

Author

Commented:
Update:  just to be clear what my goal is:

I want to run the script each day and it will delete anything older that 14 days based on the ADJAPPLYDTM column.  the whole row goes based on that one field's value.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
What are some sample values of the ADJAPPLYDTM field, or more importantly can you verify the data type of the column?

Author

Commented:
Yes, it is datetime as type of column.

Here is a format example:  2013-11-24 00:00:00
Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
PERFECT!  This is what I needed.  The CURDATE will make things easier and I must have been forgetting the semicolon accidentally.  THANKS MUCH.

Author

Commented:
One other question if you don't mind:

What would it look like to delete just the last two days as well?  I will run it as a second SQL query obviously, but last two days as well would be nice to clean up....
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Do you mean you want to keep records between '2013-12-23' and '2014-01-04', removing everything else?  Remember the "INTERVAL n DAY" portion of the code controls how you shift the original date, which in your case is today.  If last two days refers to today and yesterday, then you really want "INTERVAL 1 DAY" whereas to grab Saturday (two days ago) as well you need "INTERVAL 2 DAY".

Anyway, the query would be:
DELETE
FROM `hours`
WHERE ADJAPPLYDTM >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
;

Open in new window


Therefore, you can combine the queries into one:
DELETE
FROM `hours`
WHERE (ADJAPPLYDTM < DATE_SUB(CURDATE(), INTERVAL 14 DAY)
OR ADJAPPLYDTM >= DATE_SUB(CURDATE(), INTERVAL 1 DAY))
;

Open in new window


If I did not understand you question correctly, you may want to consider created a related question.  It will notify me as well as bring in additional Experts.

Regards,

Kevin

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.