__
-- Original Message --
FROM: Brian Dunning [EMAIL PROTECTED]
TO:mysql@lists.mysql.com
DATE: Wed, 4 Oct 2006 08:49:48 -0700
SUBJECT: Re: Deleting, skip the first n records
The offset is what I was thinking
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:
You can add an offset in the Limit statement. Look at the Select Syntax in
the docs.
There is an even simpler solution to this problem. Use your creation field
within a Where clause instead of using Order by and Limit.
-Dan
I'm trying to delete all but the newest n records.
DELETE
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 1;
DELETE FROM tablename
WHERE id NOT IN
The offset is what I was thinking of - that would be the simplest -
but as far as I can tell, delete doesn't support the offset. It's not
documented, and it gives me an error when I try it. I was hoping to
avoid two queries but it sounds like that's what I might have to do.
On Oct 4,
__
-- Original Message --
FROM: Brian Dunning [EMAIL PROTECTED]
TO:mysql@lists.mysql.com
DATE: Wed, 4 Oct 2006 08:49:48 -0700
SUBJECT: Re: Deleting, skip the first n records
The offset is what I was thinking
__
-- Original Message --
FROM: Brian Dunning [EMAIL PROTECTED]
TO:mysql@lists.mysql.com
DATE: Wed, 4 Oct 2006 08:49:48 -0700
SUBJECT: Re: Deleting, skip the first n records
The offset is what I was thinking of - that would be the simplest
__
-- Original Message --
FROM: Brian Dunning [EMAIL PROTECTED]
TO:mysql@lists.mysql.com
DATE: Wed, 4 Oct 2006 08:49:48 -0700
SUBJECT: Re: Deleting, skip the first n records
The offset is what I was thinking