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.