Page 1 of 1

deleting records

Posted: Fri Dec 09, 2005 4:19 am
by s.dot
OK, when a certain page is viewed I need to delete all records from a database EXCEPT the first 25 records returned.

For example, say I have this query:

Code: Select all

$result = mysql_query("SELECT id FROM table WHERE foo = '$bar' ORDER BY id DESC LIMIT 25");
That will return the latest 25 records, which I want to keep. However if this query returned 100 records, how could I delete records 26-100?

Posted: Fri Dec 09, 2005 5:20 am
by onion2k
Set the second parameter to be a stupidly high number ..

Code: Select all

select * from table limit 25,999999999999999999999
This is what they suggest in the MySQL manual .. http://dev.mysql.com/doc/refman/5.0/en/select.html

Posted: Fri Dec 09, 2005 8:54 am
by timvw

Code: Select all

DELETE 
FROM table
WHERE id NOT IN (
  SELECT id 
  FROM table
  ORDER BY id
  LIMIT 25
)

Posted: Fri Dec 09, 2005 10:09 am
by s.dot
both of those seem reasonable, although onions method seems to be less CPU consuming as its doing one query..

is there a preferred method between the two?

what about...

Code: Select all

DELETE FROM table WHERE foo = '$bar' LIMIT 25,999999
I've never tried something like that with a delete.. does delete have a limit clause

Posted: Fri Dec 09, 2005 10:13 am
by RobertGonzalez
DELETE and UPDATE both support limits. And when it comes to subqueries check your MySQL version. I think only MySQL 4.3+ support subqueries.

Posted: Fri Dec 09, 2005 11:07 am
by onion2k
Everah wrote:DELETE and UPDATE both support limits. And when it comes to subqueries check your MySQL version. I think only MySQL 4.3+ support subqueries.
4.1

Posted: Fri Dec 09, 2005 11:37 am
by RobertGonzalez
That's right. Most hosts use 4.0.x while the cooler features in v4 came at 4.1.x. Thanks for the correction. I knew it was somewhere in there.