Search code examples
mysqlsql-delete

Basic MySQL Commands Meaning

I'm new to SQL and I'm confused on this 'delete duplicate' question.

A table named 'Person' contains two columns: Id and Email, and I want to delete all duplicate email entries in this table, keeping only unique emails based on its smallest Id.

The answer should be like:

DELETE p1 
FROM Person p1, Person p2
WHERE p1.Email = p2.Email 
AND p1.Id > p2.Id

My question is that what is p1 here? What does the usage ‘Person p1’ mean？

Solution

  • Let's say you have a table, which is called person_office_job_join which is quite a mouthful. The p1 and p2 are aliases. that means that you can simplify references to your table.

    e.g.

    SELECT * 
FROM person LEFT JOIN person_office_job_join poj 
WHERE poj.person_id =  person.id

    is the same as

    SELECT * 
FROM person LEFT JOIN person_office_job_join  
WHERE person_office_job_join.person_id =  person.id

    In your specific case, it allows you to join on the same table, thereby finding the duplicates. Imagine a more complex query, where you want to do more stuff, while specifying the table name. having a shorthand reference is great.