EXPLAIN EXTENDED

It's very simple to delete, say, 10 top rows in MySQL:

DELETE
FROM   t_deleter
ORDER BY
          id
LIMIT 10

What if we want to delete all rows except the first 10?

Well…

We can try this:

DELETE
FROM    t_deleter
WHERE   id NOT IN
        (
        SELECT  id
        FROM    t_deleter
        ORDER BY
                id
        LIMIT 10
        )

, which results in infamous:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Ok, so let's try a single-row subquery:

DELETE
FROM    t_deleter
WHERE   id <
        (
        SELECT  id
        FROM    t_deleter
        ORDER BY
                id
        LIMIT 10, 1
        )
&#91;/sourcecode&#93;

Again no success:

<code>You can't specify target table 't_deleter' for update in FROM clause</code>

So what shall we do?

Fortunately, <strong>MySQL</strong> does support <code>DELETE</code> over multiple tables in a <code>JOIN</code>, and even more fortunately, this works:


DELETE  d.*
FROM    t_deleter d
LEFT JOIN
        (
        SELECT  id
        FROM    t_deleter
        LIMIT   10
        ) q
ON      d.id = q.id
WHERE   q.id IS NULL

SELECT  *
FROM    t_deleter