Menu
Intention of using limit in subquery is so main query run on limited records fetched from subquery. And if we keep limit outside then it makes limit useless for subquery.
Subqueries cannot manipulate their results internally, that is, a subquery cannot include the order by clause, the compute clause, or the into keyword. Correlated (repeating) subqueries are not allowed in the select clause of an updatable cursor defined by declare cursor. There is a limit of 50 nesting levels.
The MySQL LIMIT ClauseThe LIMIT clause is used to specify the number of records to return. The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
Single Row Sub Query Although this query type is formally called "single-row," the name implies that the query returns multiple columns-but only one row of results. However, a single-row subquery can return only one row of results consisting of only one column to the outer query.
Try this:
DELETE
FROM posts
WHERE id not in (
SELECT * FROM (
SELECT id
FROM posts
ORDER BY timestamp desc limit 0, 15
)
as t);
You can try this:
DELETE
p1.*
FROM
posts p1 INNER JOIN
(SELECT
id
FROM
posts
ORDER BY timestamp DESC
LIMIT 0, 15
) AS p2
ON p1.id = p2.id;
Since the newest 15 will always come from the first 15 if you order them by descending order.You can just delete any id that did not make it into the first 15. like so i just tried it and it worked fine. Hopefully it helps someone
Delete from `table` where id not in (SELECT * FROM (Select id from `table` order by id desc limit 15) as derivedTable);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With