Why this solution get 'runtime error'


  • 1
    X
    delete from Person where Id not in (select min(Id) from Person group by Email)

  • 0
    N

    I have the same question... it seems that they don't accept 'delete from' statement? I'm confused.


  • 1
    H

    It mentions "Currently, you cannot delete from a table and select from the same table in a subquery." in MySQL manual. So it will give below error:

    delete from Person where Id not in (select min(Id) from Person group by Email);

    ERROR 1093 (HY000): You can't specify target table 'Person' for update in FROM clause

    But there's a workaround for subquery. I copied from another question.

    delete from Person where id not in (select id from (select min(id) as id from Person group by email) as a);

    The nested subquery in the FROM clause creates an implicit temporary table, so it doesn't count as the same table you're deleting.


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.