Deleting duplicates using the following code

Question

I thought this should work, but as you can see I'm getting an error. The goal here is to delete duplicates by defining duplicates using two columns. The first list is all the dupes, the second list uses the row ID to ensure that one of each duplicate set is kept. Help!

DELETE FROM tbl_events_temp WHERE tbl_events_temp.event_id IN (
    SELECT F.event_id
    FROM tbl_events_temp AS F
    WHERE EXISTS (
        SELECT airport_id, event_from, Count( event_id )
        FROM tbl_events_temp
        WHERE tbl_events_temp.airport_id = F.airport_id
        AND tbl_events_temp.event_from = F.event_from
        AND tbl_events_temp.airport_id != ''
        GROUP BY tbl_events_temp.airport_id, tbl_events_temp.event_from
        HAVING Count( tbl_events_temp.event_id ) >1
    )
)
AND tbl_events_temp.event_id NOT
IN (
    SELECT Min( event_id )
    FROM tbl_events_temp AS F
    WHERE EXISTS (
        SELECT airport_id, event_from, Count( event_id )
        FROM tbl_events_temp
        WHERE tbl_events_temp.airport_id = F.airport_id
        AND tbl_events_temp.event_from = F.event_from
        GROUP BY tbl_events_temp.airport_id, tbl_events_temp.event_from
        HAVING Count( tbl_events_temp.event_id ) >1
    )
    GROUP BY airport_id, event_from
)

Error:

MySQL said: Documentation
#1093 - You can't specify target table 'tbl_events_temp' for update in FROM clause

I'm confused as to why this line exists: tbl_events_temp.airport_id = F.airport_id You alias tbl_events_temp as F, then set them equal to each other, it's like saying WHERE 1 = 1 — Hart CO, May 24 '13 at 04:08
Look at http://dev.mysql.com/doc/refman/5.7/en/subquery-errors.html, particularly the section Incorrectly used table in subquery. You can't use the same table in a subquery with an UPDATE or DELETE. — BLaZuRE, May 24 '13 at 04:13
In MySQL, you can't modify the same table which you use in the SELECT part. http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause — ATR, May 24 '13 at 04:36

score 1 · Answer 1 · answered May 24 '13 at 04:26

I think what you'll want is a DELETE with a JOIN, slapped this together quickly, so make sure the subquery correctly identifies the records to delete:

DELETE tbl_events_temp
FROM tbl_events_temp
INNER JOIN (SELECT airport_id, event_from, Count(event_id),MIN(event_id)'event_id'
            FROM tbl_events_temp
            GROUP BY tbl_events_temp.airport_id, tbl_events_temp.event_from
            HAVING Count( tbl_events_temp.event_id ) >1
            )sub
ON tbl_events_temp.event_id = sub.event_id

Deleting duplicates using the following code

1 Answers1