List:General Discussion« Previous MessageNext Message »
From:Dan Buettner Date:October 4 2006 3:41pm
Subject:Re: Deleting, skip the first n records
View as plain text  
Brian, assuming you have an identity column of some kind (we'll call
it id here), this should work:

CREATE TABLE tmptable (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

INSERT INTO tmptable (id) SELECT id FROM tablename ORDER BY creation
DESC LIMIT 10000;

DELETE FROM tablename
WHERE id NOT IN
  (SELECT id FROM tmptable);

TRUNCATE TABLE tmptable; /* or DROP TABLE tmptable if this is a
one-time thing */

What would be ideal is this below, but I get an error that MySQL
(5.0.21) doesn't yet support LIMIT in subqueries:

DELETE FROM tablename
WHERE id NOT IN
  (SELECT id FROM tablename ORDER BY creation DESC LIMIT n)

Dan


On 10/4/06, Brian Dunning <brian@stripped> wrote:
> I'm trying to delete all but the newest n records.
>
> DELETE FROM tablename ORDER BY creation DESC LIMIT=n
>
> This does the opposite of what I want. Is there some way to tell it
> to start the delete after n and delete all the remaining records?
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
Thread
Deleting, skip the first n recordsBrian Dunning4 Oct
  • Re: Deleting, skip the first n recordsDan Julson4 Oct
    • Re: Deleting, skip the first n recordsBrian Dunning4 Oct
  • Re: Deleting, skip the first n recordsDan Buettner4 Oct
re[2]: Deleting, skip the first n recordsRob Desbois4 Oct
  • Re: re[2]: Deleting, skip the first n recordsBrian Dunning4 Oct
    • Re: Deleting, skip the first n recordsDan Julson4 Oct
      • Re: Deleting, skip the first n recordsDuĊĦan Pavlica5 Oct