3

I have:

mysql_query("DELETE FROM mytable ORDER BY id DESC LIMIT 3, 999999999;") 
or die(mysql_error());

But it doesn't seem to like it. I want to keep the most recent three rows and delete everything else. Thoughts?

Followup - thanks for the simultaneous jinx!

I tried the code and got this version error:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

I checked my hosting and I have: MySQL version 5.5.36-cll-lve

I didn't expect this. What version do I need for limits? Is there another way to write this that will work?

Shazboticus S Shazbot
  • 1,289
  • 2
  • 16
  • 27
  • In PHP 5.5 you should be getting warnings that `mysql_query` is a deprecated interface and should not be used in new applications and will be removed in future versions of PHP. A modern replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/). If you're new to PHP, a guide like [PHP The Right Way](http://www.phptherightway.com/) can help explain best practices. – tadman Oct 01 '14 at 16:03
  • I'm not a php guy nor a mysql guy. I simply am looking for a faster way to retrieve and store data that I am using with a node.js app that is offsite and pulling from google spreadsheets while doing some complicated oauth authentications. I really just need a simple solution here. If you have a written solution that solves the above problem using PDOs and will not give me a similar error to what's written above, feel free to post it. – Shazboticus S Shazbot Oct 01 '14 at 16:10
  • @ShazboticusSShazbot Have you tried Mark's edited answer? Reload if you have not seen the changes made. – Funk Forty Niner Oct 01 '14 at 16:19
  • Trying how, missed his edited answer. TY – Shazboticus S Shazbot Oct 01 '14 at 16:20
  • You're welcome. It's best placing comments under answers given, in order to let them know that it did not work or other details why. @ShazboticusSShazbot – Funk Forty Niner Oct 01 '14 at 16:21
  • Agreed. I put the first one up there because 3 people all responded simultaneously with very similar answers and I lol'd – Shazboticus S Shazbot Oct 01 '14 at 16:24
  • 1
    I updated my answer and tested it on SQL Fiddle. I can't believe mySql makes it *this much* of a pain!! `+1` for the question! – Mike Christensen Oct 01 '14 at 16:26
  • @ShazboticusSShazbot You'd probably have a better time using the NodeJS MySQL interface if you're already working with it in the first place. PHP for this unnecessary, especially for a simple extract and transform operation. – tadman Oct 01 '14 at 16:44
  • I would but the node.js service isn't running on my personal server - it's on heroku which has some latency. My personal server is linux and shared, so node can't be installed on it. It's a bit hacked together, but it's doing some pretty tricky things :) – Shazboticus S Shazbot Oct 01 '14 at 17:02

4 Answers4

9

You'd think your first attempt would actually work. However, the LIMIT keyword in the MySql DELETE command only supports a maximum number of rows, not a range of values to delete. This is why you see the error you're getting. Next, you'd think (as I thought) something like this would work:

DELETE FROM mytable WHERE id NOT IN
   (SELECT id FROM mytable ORDER BY id DESC LIMIT 3);

However, MySql doesn't seem to support the IN or NOT IN operators on a DELETE statement either. Next, you'd think something like this would work:

DELETE FROM mytable WHERE id <
   (SELECT id FROM mytable ORDER BY id DESC LIMIT 2,1)

However, it seems MySql doesn't support this idea either. There are some other posts that say you need to build a temp table, which seems insanely silly! However, with a bunch of nested selects and alias hackery, I was able to get this working:

DELETE FROM mytable WHERE id <
  (select id FROM (SELECT * FROM myTable) as t ORDER BY id DESC LIMIT 2,1);

Working Fiddle

It's unbelievable MySql makes it this difficult! Maybe it's time for a better database, such as PostgreSQL? It will just work like you'd expect.

Community
  • 1
  • 1
Mike Christensen
  • 88,082
  • 50
  • 208
  • 326
7
DELETE FROM mytable WHERE id < ( 
    SELECT id FROM mytable ORDER BY id DESC LIMIT 2,1 
)

If your version of MySQL complains about subqueries in a delete statement, try

DELETE FROM mytable WHERE id < ( 
    SELECT id FROM ( 
        SELECT id FROM mytable ORDER BY id DESC LIMIT 2,1 
    ) 
)
Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • OP states (in an edit): *"I tried the code and got this version error:"* - *"This version of MySQL doesn't yet support '`LIMIT` & `IN/ALL/ANY/SOME` subquery'"* - So the OP can't use `LIMIT`? Or, is it just a sub-query issue? – Funk Forty Niner Oct 01 '14 at 16:15
  • @Fred-ii- - Uncertain at this point, because I haven't tried it; but certainly the sub-suquery approach works with some deletes that I've tried in the past and MySQL has complained vociferously – Mark Baker Oct 01 '14 at 16:18
  • Can you post a working SQL Fiddle? I still can't get this working, and other SO posts are saying you need to create a temp table. – Mike Christensen Oct 01 '14 at 16:21
  • Gave it a try, got this - "Every derived table must have its own alias" – Shazboticus S Shazbot Oct 01 '14 at 16:22
  • Given your experience Mark, I'm sure your code is kosher. – Funk Forty Niner Oct 01 '14 at 16:22
  • *"However, Mark Baker was on the right track and he deserves a +1 for a nearly working answer."* ([`Mike`](http://stackoverflow.com/questions/26145350/php-mysql-delete-all-rows-except-most-recent-3#comment40984688_26145420)). Indeed. +1 here too. – Funk Forty Niner Oct 01 '14 at 16:35
  • My bad for not testing else I'd have spotted the lack of alias, but thanks for the boonie points guys – Mark Baker Oct 01 '14 at 16:49
5

Filtering the first 3 ID's and delete the rest, you can do it with this query:

 DELETE FROM mytable WHERE id NOT IN ( 
  SELECT id FROM mytable ORDER BY id DESC LIMIT 3 
 )
S.Visser
  • 4,645
  • 1
  • 22
  • 43
1

Try this:

DELETE FROM mytable WHERE id NOT IN (
    SELECT id FROM mytable
    ORDER BY id DESC
    LIMIT 3
)

I'm not sure what you were trying to do with the second large limit value, but subqueries like this generally make working in SQL much more simple.

Carson
  • 45
  • 6