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