Solved

Deleting records by timestamp

Posted on 2009-04-11
6
342 Views
Last Modified: 2012-05-06
Hi,

I have a table which looks like:

    id          |    name   |   date_created
   ----------------------------------------------
   varchar     varchar      TIMESTAMP


I want to delete the oldest N records from the above table so I end up with max 50 records in the table. For example, if the table has 1000 records, I'd like to delete the oldest 950 records. How can I do this? I guess I need to sort the records by timestamp, then find the 50th record, record its timestamp value, then delete all records with a timestamp 'greater' than that...any info would be great,

Thanks
0
Comment
Question by:DJ_AM_Juicebox
[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
6 Comments
 
LVL 9

Expert Comment

by:LinuxNubb
ID: 24123248
If you know exactly how many records you have, you could add a 'LIMIT 950' onto the end of the delete query.

Maybe:

DELETE FROM table ORDER BY date_created DESC LIMIT (SELECT COUNT(id) FROM table - 50)

?

Worth a test.  TEST would be the key!
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24123336
This will solve you out:

DELETE FROM table
WHERE id IN
( SELECT t1.id FROM table t1 LEFT OUTER JOIN (SELECT id FROM table ORDER BY date_created DESC LIMIT 50) t2 ON t1.id = t2.id where t2.id is null)
0
 
LVL 14

Expert Comment

by:racek
ID: 24123477
DELETE FROM your_table
WHERE id  NOT IN
( SELECT id,date_created
    FROM your_table
    ORDER BY date_created DESC LIMIT 50);
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 41

Expert Comment

by:Sharath
ID: 24123536
The date_created column should not be present in the SELECT clause in racek post. corrected that.
DELETE FROM your_table
WHERE id  NOT IN
( SELECT id
    FROM your_table 
    ORDER BY date_created DESC LIMIT 50);

Open in new window

0
 
LVL 14

Expert Comment

by:racek
ID: 24124810
sorry LIMIT is forbidden in subqueries :-)
CREATE TABLE del_rows 
SELECT id FROM your_table ORDER BY date_created DESC LIMIT 50
 
DELETE FROM your_table
WHERE id  NOT IN
( SELECT id
    FROM del_rows 
    ORDER BY date_created DESC LIMIT 50);
 
DROP table del_rows;

Open in new window

0
 
LVL 14

Accepted Solution

by:
racek earned 500 total points
ID: 24124814
oops too much copy & paste :-)
CREATE TABLE del_rows 
SELECT id FROM your_table ORDER BY date_created DESC LIMIT 50
 
DELETE FROM your_table
WHERE id  NOT IN (SELECT id  FROM del_rows);
 
DROP table del_rows;

Open in new window

0

Featured Post

Webinar: Security & Encryption in the MySQL world

Join Percona’s Solutions Engineer, Dimitri Vanoverbeke as he presents “Security and Encryption in the MySQL world” on Thursday, July 6, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

726 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