[Leetcode] Deleteduplicateemails, Problem solving report

Topics

SQL query to delete all duplicate e-mail entries in a table named person, keeping unique emails based on its smallest Id.

Id	Email
1	[Email protected]
2	[Email protected]
3	[Email protected]

Id is the primary key, column for this table.
For example, after running your query, the above person table should has the following rows:

Id	Email
1	[Email protected]
2	[Email protected]

# Ideas

The title means: Find the duplicate email records from the person table, keep only the records with the smallest ID, and delete the other duplicate records.

Because it is to find first, then delete, so we also take two steps:

1. Find the duplicate email records

select p1.Id from Person as p1 inner join Person as p2 where p1.Email = p2.Email and p1.ID > p2.ID;

1. Delete duplicate records

delete from Person where Id in ($sql1);

Although the idea is good, but it is wrong to write:

delete from Person where Id in (select p1.Id from Person as p1 inner join Person as p2 where p1.Email = p2.Email and p1.ID > p2.ID);

The error message is:

You can‘t‘Personforin FROM clause

This means: In MySQL, it is forbidden to specify the target table to be updated in the FROM clause.

Therefore, you need to use the MySQL-defined delete syntax:

1. Delete all the records in the data table T1 that have matched the ID values in the data table T2:

delete t1 from t1,t2 where t1.id = t2.id;ordelete from t1 using t1,t2 where t1.id = t2.id;

1. From the data table T1 in the data table t2 no matching records found and deleted

delete t1 from t1 left join t2 on t1.id=t2.id where t2.id is null;

AC SQL

delete p1 from Person as p1 inner join Person as p2 on p1.ID > p2.ID and p1.Email = p2.Email;

[Leetcode]deleteduplicateemails, Problem solving report