homepage Welcome to WebmasterWorld Guest from 107.20.109.52
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Delete old records (without date field)
techtheatre




msg:4161102
 5:50 am on Jun 29, 2010 (gmt 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!

 

dreamcatcher




msg:4161115
 6:46 am on Jun 29, 2010 (gmt 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

techtheatre




msg:4161137
 7:51 am on Jun 29, 2010 (gmt 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.

topr8




msg:4161143
 7:59 am on Jun 29, 2010 (gmt 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.

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




msg:4161190
 10:16 am on Jun 29, 2010 (gmt 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!

dreamcatcher




msg:4161530
 6:33 pm on Jun 29, 2010 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved