CODEWITHSUNDEEP
mysqlsql-delete
Elaine Yang
Elaine Yang

Reputation: 123

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？

Upvotes: 1

Views: 72

Answers (1)

JoSSte
JoSSte

Reputation: 3372

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.

Upvotes: 1

Related Questions