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
>
>
