Reputation: 1299
I have:
mysql_query("DELETE FROM mytable ORDER BY id DESC LIMIT 3, 999999999;")
or die(mysql_error());
But it doesn't seem to like it. I want to keep the most recent three rows and delete everything else. Thoughts?
Followup - thanks for the simultaneous jinx!
I tried the code and got this version error:
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
I checked my hosting and I have: MySQL version 5.5.36-cll-lve
I didn't expect this. What version do I need for limits? Is there another way to write this that will work?
Upvotes: 3
Views: 140
Reputation: 91734
You'd think your first attempt would actually work. However, the LIMIT
keyword in the MySql DELETE
command only supports a maximum number of rows, not a range of values to delete. This is why you see the error you're getting. Next, you'd think (as I thought) something like this would work:
DELETE FROM mytable WHERE id NOT IN
(SELECT id FROM mytable ORDER BY id DESC LIMIT 3);
However, MySql doesn't seem to support the IN
or NOT IN
operators on a DELETE
statement either. Next, you'd think something like this would work:
DELETE FROM mytable WHERE id <
(SELECT id FROM mytable ORDER BY id DESC LIMIT 2,1)
However, it seems MySql doesn't support this idea either. There are some other posts that say you need to build a temp table, which seems insanely silly! However, with a bunch of nested selects and alias hackery, I was able to get this working:
DELETE FROM mytable WHERE id <
(select id FROM (SELECT * FROM myTable) as t ORDER BY id DESC LIMIT 2,1);
It's unbelievable MySql makes it this difficult! Maybe it's time for a better database, such as PostgreSQL? It will just work like you'd expect.
Upvotes: 9
Reputation: 212522
DELETE FROM mytable WHERE id < (
SELECT id FROM mytable ORDER BY id DESC LIMIT 2,1
)
If your version of MySQL complains about subqueries in a delete statement, try
DELETE FROM mytable WHERE id < (
SELECT id FROM (
SELECT id FROM mytable ORDER BY id DESC LIMIT 2,1
)
)
Upvotes: 7
Reputation: 45
Try this:
DELETE FROM mytable WHERE id NOT IN (
SELECT id FROM mytable
ORDER BY id DESC
LIMIT 3
)
I'm not sure what you were trying to do with the second large limit value, but subqueries like this generally make working in SQL much more simple.
Upvotes: 1
Reputation: 4735
Filtering the first 3 ID's and delete the rest, you can do it with this query:
DELETE FROM mytable WHERE id NOT IN (
SELECT id FROM mytable ORDER BY id DESC LIMIT 3
)
Upvotes: 5