MyBB Community Forums

DELETE FROM mytable WHERE pid NOT IN (SELECT pid FROM mytable ORDER BY pid DESC LIMIT 10)

How would I accomplish that without using a subquery? Looks like you can't use LIMIT in subqueries for some reason.

Maybe like this:

DELETE FROM mytable ORDER BY pid DESC LIMIT 10

Not what I expected, maybe the other query would've done that too...

I want to keep the ten most recent rows, and delete everything else. That one deletes the bottom ten, so running it enough times will empty the table.

Does it have to be pure SQL? I mean, you could get the total number of rows with php, the limit it to begin at ten and delete the rest

(2011-01-09, 02:47 AM)CAwesome Wrote: [ -> ]Not what I expected, maybe the other query would've done that too...

I want to keep the ten most recent rows, and delete everything else. That one deletes the bottom ten, so running it enough times will empty the table.

Oops, I didn't see the NOT in your query. It was late last night

The only SQL-only solution I found to work is using a subquery in a subquery:

DELETE FROM test WHERE pid NOT IN(SELECT pid FROM (SELECT pid FROM test ORDER BY pid DESC LIMIT 10) AS t)

If you don't want use it the subquery in subquery solution the only other solution is using PHP to select the most recent 10 ids and then delete all the others.

$ids = array();
$query = mysql_query("SELECT pid FROM test ORDER BY pid DESC LIMIT 10");
while($row = mysql_fetch_assoc($query))
{
      $ids[] = (int)$row['pid'];
}

mysql_query("DELETE FROM test WHERE pid NOT IN(".implode(",",$ids).")");