CODEWITHSUNDEEP
phpmysql
Shazboticus S Shazbot
Shazboticus S Shazbot

Reputation: 1299

PHP - MySQL - Delete all rows except most recent 3 & This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

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?

Upvotes: 3

Views: 140

Answers (4)

Mike Christensen
Mike Christensen

Reputation: 91734

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.

Upvotes: 9

Mark Baker
Mark Baker

Reputation: 212522

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 
    ) 
)

Upvotes: 7

Carson
Carson

Reputation: 45

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.

Upvotes: 1

S.Visser
S.Visser

Reputation: 4735

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 
 )

Upvotes: 5

Related Questions