If you don't know the cutoff_date you can simply find it by
SELECT creation FROM tablename ORDER BY creation DESC LIMIT n, 1
which gives you the creation of the n-th newest record and then you
can use Dan's solution
DELETE FROM tablename WHERE creation < cutoff_date
HTH,
Dusan
Dan Julson napsal(a):
> Brian,
>
> My bad in steering you into the offset direction. You are right. Offset
> cannot be used within a delete statement. However, if I am reading your
> email correctly, you could specify a cutoff date and use that in the Delete
> statement like this:
>
> DELETE FROM tablename WHERE creation < cutoff_date
>
> -Dan
>
> My bad, the = was my own typo just in the email, it's not in my
> actual query. I've tried using offset (delete...limit 50,999999) and
> it returns an error, and it is not documented (search that page you
> referenced for "offset").
>
> On Oct 4, 2006, at 9:04 AM, Rob Desbois wrote:
>
>> DELETE does support the offset (http://dev.mysql.com/doc/refman/5.0/
>> en/delete.html) the problem is you have an erroneous equals character:
>>
>> You wrote:
>> DELETE FROM tablename ORDER BY creation DESC LIMIT=n
>> You need:
>> DELETE FROM tablename ORDER BY creation DESC LIMIT offset, count
>>
>> HTH,
>> --Rob
>>
>>
>>> 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, 2006, at 8:37 AM, Dan Julson wrote:
>>
>>> 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 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=robert.desbois@stripped
>>
>>
>> ______________________________________________________________________
>> This email has been scanned by the MessageLabs Email Security System.
>> For more information please visit http://www.messagelabs.com/email
>> ______________________________________________________________________
>>
>>
>> <
>>
>> ---------- Original Message ----------
>>
>> FROM: Brian Dunning <brian@stripped>
>> TO: mysql@stripped
>> 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 -
>> 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, 2006, at 8:37 AM, Dan Julson wrote:
>>
>>> 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 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=robert.desbois@stripped
>>
>>
>> ______________________________________________________________________
>> This email has been scanned by the MessageLabs Email Security System.
>> For more information please visit http://www.messagelabs.com/email
>> ______________________________________________________________________
>>
>>
>> ______________________________________________________________________
>> This email has been scanned by the MessageLabs Email Security System.
>> For more information please visit http://www.messagelabs.com/email
>> ______________________________________________________________________
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?
>> unsub=brian@stripped
>>
>
>
