deleting records

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

deleting records

Post 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?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Code: Select all

DELETE 
FROM table
WHERE id NOT IN (
  SELECT id 
  FROM table
  ORDER BY id
  LIMIT 25
)
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Post Reply