Solved

Deleting records by timestamp

Posted on 2009-04-11
6
337 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
6 Comments
 
LVL 9

Expert Comment

by:LinuxNubb
Comment Utility
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
Comment Utility
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
Comment Utility
DELETE FROM your_table
WHERE id  NOT IN
( SELECT id,date_created
    FROM your_table
    ORDER BY date_created DESC LIMIT 50);
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now