0

i am trying to solve this sql problem on leetcode - delete duplicate emails: https://leetcode.com/problems/delete-duplicate-emails/

here's my solution

DELETE FROM Person
WHERE id NOT in (
     SELECT max(id) FROM Person
     GROUP BY email 
     HAVING COUNT(email) >1 
)

I got the erorr: You can't specify target table 'Person' for update in FROM clause my understanding is that the error is saying you can't reference the table that's being modified. i tried to give the subquery alias to get around this, but it does not work. please help me to get around this error? and more importantly, please tell me if my understanding of the situation is right or wrong.

Simon Zhao
  • 5
  • 2
  • Create a temporary table to store the result of the of the sub query and then you could call the ids from the temporary table to delete the rows in the first table – Blip May 08 '23 at 08:01

1 Answers1

0

You can use temporary table to store the result of the subquery and delete the duplicate emails. Temporary table is like this,

CREATE TEMPORARY TABLE temp_table
SELECT max(id) as id FROM Person
GROUP BY email 
HAVING COUNT(email) >1;

Your main delete query like this,

DELETE FROM Person
WHERE id NOT IN (SELECT id FROM temp_table);
Free Helper
  • 26
  • 4