Deleting records by timestamp

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
DJ_AM_JuiceboxAsked:
Who is Participating?
 
racekConnect With a Mentor Commented:
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
 
LinuxNubbCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
racekCommented:
DELETE FROM your_table
WHERE id  NOT IN
( SELECT id,date_created
    FROM your_table
    ORDER BY date_created DESC LIMIT 50);
0
 
SharathData EngineerCommented:
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
 
racekCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.