Deleting duplicates using the following code

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!

我认为这应该有效,但你可以看到我收到了一个错误。这里的目标是通过使用两列定义重复项来删除重复项。第一个列表是所有dupes,第二个列表使用行ID来确保保留每个重复集中的一个。救命!

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 

1 个解决方案

#1

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:

我认为你想要的是一个带有JOIN的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