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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
