Welcome to WebmasterWorld Guest from 54.146.246.4

Forum Moderators: open

Message Too Old, No Replies

Delete old records (without date field)

     

techtheatre

5:50 am on Jun 29, 2010 (gmt 0)

5+ Year Member



I have a list of entries in my database where I always only use the most recent 100 entries. I successfully accomplish this with:
SELECT * FROM table WHERE something='stuff' ORDER BY field DESC LIMIT 100;


In order to begin cleaning up the data, is there a command that will delete the records beyond the initial 100? I can't just do
DELETE FROM table WHERE something='stuff' ORDER BY field DESC LIMIT 100;
because that will delete my first 100 records...i want record 101+ (from the queried results of course...not the whole table).

Also, i do not have a date field, so this is not deletion based upon a date range...just based upon result number.

THANKS!

dreamcatcher

6:46 am on Jun 29, 2010 (gmt 0)

WebmasterWorld Senior Member dreamcatcher is a WebmasterWorld Top Contributor of All Time 10+ Year Member



CREATE table2 (SELECT * FROM table WHERE something='stuff' ORDER BY field DESC LIMIT 100);
RENAME TABLE table TO old_table,table2 TO table;
DROP TABLE old_table;

dc

techtheatre

7:51 am on Jun 29, 2010 (gmt 0)

5+ Year Member



I am looking for something I can set up in a cron job (or other) to automatically clean out older records periodically...so it needs to keep the same table and structure, just with some rows deleted.

topr8

7:59 am on Jun 29, 2010 (gmt 0)

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



dreamcatcher - if there were various permissions on the original table i think the newly renamed table won't have them ... i'm happy to stand corrected.

techtheatre - you could try this

DELETE FROM table WHERE field NOT IN (SELECT field FROM table WHERE something='stuff' ORDER BY field DESC LIMIT 100);

techtheatre

10:16 am on Jun 29, 2010 (gmt 0)

5+ Year Member



I think that should work. It will take some tweaking to get it right, but i think this is absolutely what i was looking for. THANKS!

dreamcatcher

6:33 pm on Jun 29, 2010 (gmt 0)

WebmasterWorld Senior Member dreamcatcher is a WebmasterWorld Top Contributor of All Time 10+ Year Member



dreamcatcher - if there were various permissions on the original table i think the newly renamed table won't have them ... i'm happy to stand corrected.

Nope, you may well be correct.