[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Delete Range of dates in MySQL

Posted on 2014-01-06
7
Medium Priority
?
352 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.
0
Comment
Question by:weklica
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 

Author Comment

by:weklica
ID: 39759742
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.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39759754
What are some sample values of the ADJAPPLYDTM field, or more importantly can you verify the data type of the column?
0
 

Author Comment

by:weklica
ID: 39759762
Yes, it is datetime as type of column.

Here is a format example:  2013-11-24 00:00:00
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 39759769
Here is an example:
DELETE
FROM `hours`
WHERE ADJAPPLYDTM < DATE_SUB(CURDATE(), INTERVAL 14 DAY)
;

Open in new window


The code should delete rows with a date less than '2013-12-23' if run today.  If you want to control the date, substitute CURDATE() for literal like '2013-12-30', leveraging STR_TO_DATE() if necessary.
0
 

Author Closing Comment

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

Author Comment

by:weklica
ID: 39759812
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....
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39759834
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
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question