Help with a delete query

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

Help with a delete query

Post by s.dot »

I have a table. 8) Each user should have 30 rows in this table, corresponding to the last 30 days (one new row is inserted per day).

I want to delete all the rows (for each user) that are older than the 30 newest records.

For example there are three users bob, jack, and jim. They each have 100 records. I want to delete the all except the newest 30 records.

I could effectively do something like this for each user:

Code: Select all

DELETE FROM `table` WHERE `id` NOT IN (SELECT `id` FROM `table` WHERE `username` = 'bob' ORDER BY `id` DESC LIMIT 0, 30) AND `username` = 'bob'
However, I don't want to loop through thousands of usernames doing thousands of queries. So I won't know the usernames to put in the where clause of the subquery.

I was thinking I could do a group by `username` somewhere, but I'm not sure how to do it using subqueries.

Is this possible in one query?
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
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Help with a delete query

Post by Kieran Huggins »

not sure if it would work, but my instinct would be along the lines of:

Code: Select all

DELETE FROM TABLE GROUP BY username ORDER BY id ASC LIMIT 30, 10000
but it seems to me that keeping the data and modifying your select to just show the most current would be a better practice. SQL data is relatively small and storage is cheap.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: Help with a delete query

Post by s.dot »

Kieran Huggins wrote:not sure if it would work, but my instinct would be along the lines of:

Code: Select all

DELETE FROM TABLE GROUP BY username ORDER BY id ASC LIMIT 30, 10000
but it seems to me that keeping the data and modifying your select to just show the most current would be a better practice. SQL data is relatively small and storage is cheap.
Yeah, that's what I've been doing. I will try playing around with your query and see if I can get it to work. My line of thinking is that the extra records are 'baggage'. I keep 30 days history and display it in a graph. I don't need more than that. And I was thinking that since now there's getting around 400,000 and growing rows that the queries may be getting slower. Will have to run some benchmarks to see.
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
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: Help with a delete query

Post by Ollie Saunders »

This is premature optimization. Also this is undoubtedly way easier to do in two queries.
The cloest thing I could come up with is this:

Code: Select all

mysql> delete from `a` where id in (select 10); -- works! :-)
Query OK, 1 row affected (0.00 sec)
mysql> delete from `a` where `id` in (select `id` LIMIT 30,999999999999); -- doesn't work :-(
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.51a   | 
+-----------+
1 row in set (0.00 sec)
Even if it did work I don't like the 99999 thing. But there's not really getting round that either:
MySQL Manual wrote: To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

Code: Select all

SELECT * FROM tbl LIMIT 95,18446744073709551615;
Which strikes me at pretty ridiculous.

Here's another query that doesn't work:

Code: Select all

SELECT * FROM a LIMIT 30,(SELECT count(*) - 30)
Post Reply