Welcome to WebmasterWorld Guest from 54.144.44.9

Forum Moderators: open

Message Too Old, No Replies

Delete old records (without date field)

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

Junior Member

5+ Year Member

joined:May 12, 2007
posts: 91
votes: 0


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!
6:46 am on June 29, 2010 (gmt 0)

Senior Member

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

joined:Mar 30, 2003
posts:3719
votes: 0


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
7:51 am on June 29, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:May 12, 2007
posts:91
votes: 0


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.
7:59 am on June 29, 2010 (gmt 0)

Senior Member

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

joined:Apr 19, 2002
posts:3196
votes: 12


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);
10:16 am on June 29, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:May 12, 2007
posts:91
votes: 0


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!
6:33 pm on June 29, 2010 (gmt 0)

Senior Member

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

joined:Mar 30, 2003
posts:3719
votes: 0


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.