delete from Person where Id not in (select Id from (select min(Id) as Id from Person group by Email ) p );
Can you explain more why there will be an error if we are not adding this alias? Thanks
@car2116 f you're doing an UPDATE/INSERT/DELETE on a table, you can't reference that table in an inner query (you can however reference a field from that outer table...)
@james97 I think 'p' is a temp_name for the new table(min(Id)). Otherwise mySQL will not accept the query.
Could anyone explain to me how min(id) works and why it's being used?
I typed the following. Would this one work?
DELETE FROM PERSON
WHERE EMAIL IN (
GROUP BY EMAIL
HAVING COUNT(*) > 1 )
@dullangle as hardware gets better, new submissions perform better, so does my solution. Now it's 745 ms beats 97.41% submissions.
@sqlworld1 your solution ended up deleting all rows where email have dups. you need to preserve one row from each email, with a minimum id number.
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.