Bryan Bryan - 2 months ago 8
MySQL Question

MySQL delete statement based on sub-select with multiple return values

MySQL delete statement based on sub-select with multiple return values.

Here is what I am doing now:

DELETE FROM `dnsstats` WHERE id NOT IN
(

SELECT id FROM
(
SELECT id FROM `dnsstats` WHERE peerhost = 'x.x.x.243' ORDER BY id DESC LIMIT 500
) foo
)
AND id NOT IN
(
SELECT id FROM
(
SELECT id FROM `dnsstats` WHERE peerhost = 'x.x.x.40' ORDER BY id DESC LIMIT 500
) foo2
)
AND id NOT IN
(
SELECT id FROM
(
SELECT id FROM `dnsstats` WHERE peerhost = 'x.x.x.50' ORDER BY id DESC LIMIT 500
) foo3
);


This works great, but I would like to select the individual IP addresses dynamically like so:

SELECT peerhost FROM `dnsstats` GROUP BY peerhost;


..and delete based on those returned values.

This is what i tried (and failed):

DELETE FROM `dnsstats` WHERE id NOT IN
(
SELECT id FROM
(
SELECT id FROM `dnsstats` WHERE peerhost = (
SELECT peerhost FROM `dnsstats` GROUP BY peerhost;
) ORDER BY id DESC LIMIT 500
) foo
);


Any idea what I can do to make this work? Thanks!

Answer

I think you might need to remove the semicolon in SELECT peerhost FROM dnsstats GROUP BY peerhost;

The query will run but I don't think you'll get the correct result: Earlier you were getting 500 from each id related to a peerhost,

now you'll be getting total 500 for all. You need to change the query.

==> Edit: Try this Bryan

DELETE FROM dnsstats WHERE id IN
(
    SELECT e.id FROM
    (
        SELECT d.id FROM dnsstats d
        LEFT JOIN
    (
            SELECT a.peerhost,
                (SELECT id FROM dnsstats WHERE peerhost = a.peerhost
                    ORDER BY id DESC LIMIT 499,1) id
            FROM 
            (SELECT peerhost FROM dnsstats GROUP BY peerhost) a
    ) c
    ON d.peerhost = c.peerhost
WHERE d.id < c.id) e);