EXPLAIN EXTENDED

How to create fast database queries

Keeping rows

Comments enabled. I *really* need your comment

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
        )

Again no success:

You can't specify target table 't_deleter' for update in FROM clause

So what shall we do?

Fortunately, MySQL does support DELETE over multiple tables in a JOIN, 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
9990 rows affected by the last command, no resultset returned
SELECT  *
FROM    t_deleter
id value
1 Value 1
2 Value 2
3 Value 3
4 Value 4
5 Value 5
6 Value 6
7 Value 7
8 Value 8
9 Value 9
10 Value 10
10 rows fetched in 0.0003s (0.0014s)

Written by Quassnoi

April 7th, 2009 at 11:00 pm

Posted in MySQL

Leave a Reply