List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:April 16 2003 10:28am
Subject:Re: Delete all but (n) most recent records?
View as plain text  
You should be able to use an order by & limit with delete, in recent 
versions (>= 4.0, I think):

	DELETE FROM table ORDER BY id DESC LIMIT 200

See

	http://www.mysql.com/doc/en/DELETE.html

for more info. I'm assuming that id would normally be an 
autoincrement field, although this would work with a timestamp. This 
would take care of any gaps in the id sequence. Normal warnings about 
untested query apply...

	-steve


At 11:46 AM +1000 4/16/03, Daniel Kasak <dkasak@stripped> wrote:
>Don Read <dread@stripped> wrote:
>
>>SELECT (@top:=MAX(id)) FROM table;
>>DELETE FROM table WHERE id < (@top - 200);
>>
>>Regards,
>>
>No, you can't do that either.
>That assumes that you have 200 consecutive IDs.
>If someone has been deleting records, and causing gaps in the 
>primary keys, then you will not get the desired result.
>
>--
>Daniel Kasak
>IT Developer
>* NUS Consulting Group*
>Level 18, 168 Walker Street
>North Sydney, NSW, Australia 2060
>T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
>email: dkasak@stripped
>website: www.nusconsulting.com
>


-- 
+------------------------------------------------------------------------+
| Steve Edberg                                      sbedberg@stripped |
| University of California, Davis                          (530)754-9127 |
| Programming/Database/SysAdmin               http://pgfsun.ucdavis.edu/ |
+------------------------------------------------------------------------+
| SETI@Home: 1001 Work units on 23 oct 2002                              |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
+------------------------------------------------------------------------+
Thread
Delete all but (n) most recent records?M Wells15 Apr
  • RE: Delete all but (n) most recent records?Peter Monk16 Apr
    • Re: Delete all but (n) most recent records?Daniel Kasak16 Apr
      • Re: Delete all but (n) most recent records?Don Read16 Apr
        • Re: Delete all but (n) most recent records?Daniel Kasak16 Apr
          • Re: Delete all but (n) most recent records?Steve Edberg16 Apr
            • Re: Delete all but (n) most recent records?Stephen Giese16 Apr
        • Re: Delete all but (n) most recent records?Santino16 Apr
        • Re: Delete all but (n) most recent records?Santino16 Apr
          • RE: Delete all but (n) most recent records?Jon Frisby16 Apr
            • RE: Delete all but (n) most recent records?Jon Frisby16 Apr
              • RE: Delete all but (n) most recent records?M Wells19 Apr
                • RE: Delete all but (n) most recent records?Jon Frisby19 Apr
          • RE: Delete all but (n) most recent records?M Wells19 Apr
            • Re: Delete all but (n) most recent records?Peter Brawley19 Apr
        • Re: Delete all but (n) most recent records?Jerry17 Jul
RE: Delete all but (n) most recent records?Andrew Braithwaite16 Apr