[SQL] LeetCode 196: Delete Duplicate Emails

[@NhiNguyen-yo2pm]

When running your second approach, I got the runtime error on LeetCode ("You can't specify target table 'p' for update in FROM clause")
delete p from Person p
where id != (select min(id) from Person
where email = p.email)

[@sankhadipdebnath5654]

Hi, thanks for your comment. What platform you are using could you please let me know?

[@NhiNguyen-yo2pm]

@@sankhadipdebnath5654 Oh, I used MySQL

[@sankhadipdebnath5654]

@@NhiNguyen-yo2pm I think there is a problem with this logic with MySql, will you please try in SQL server and let me know if you face any difficulty.

[@sankhadipdebnath5654]

Using Join:
delete p2
from person p1 join person p2
on p1.email=p2.email
and p1.id

[@phanphancharee4838]

I’m confused why deleted p2 ?

[@sankhadipdebnath5654]

Hi, we are deleting p2 as p2 table after join contains the higher IDs but we want only unique and also need to keep the lower IDs of duplicates. Hope this helps.
If you still have doubts try to Create it by your own SQL platform and try writing select query, if you have still any doubts please comment.

[@chandanroy3634]

explain plzzzz....you were not explaining anything

[@sankhadipdebnath5654]

Thanks for Watching !! I have shown 2 different approach to solve this problem. In the solution without using join first the correlated query will work and find all the min IDs for each email (Notice that I have used the person table and p table which is actually same). Once we know the min IDs for each table we will delete all other IDs which are not minimum for each email. Same approach is for join solution as well. Hope this helps.