homepage Welcome to WebmasterWorld Guest from 54.167.138.53
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, Moderator: open

Databases Forum

    
Delete old records (without date field)
techtheatre

5+ Year Member



 
Msg#: 4161100 posted 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

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



 
Msg#: 4161100 posted 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

5+ Year Member



 
Msg#: 4161100 posted 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

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



 
Msg#: 4161100 posted 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

5+ Year Member



 
Msg#: 4161100 posted 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

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



 
Msg#: 4161100 posted 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