The 365 Data Science team is proud to invite you to our own community forum. A very well built system to support your queries, questions and give the chance to show your knowledge and help others in their path of becoming Data Science specialists.
Ask
Anybody can ask a question
Answer
Anybody can answer
Vote
The best answers are voted up and moderated by our team

SQL DELETE Statement

SQL DELETE Statement

0
Votes
1
Answer

Hi 
How can I delete a duplicated row from a table? I want to keep one record and delete whichever are duplicated.
Thank you

#, #
1 Answer

365 Team
0
Votes

Hi Prakash!
Thanks for reaching out.
We have not done this in the course, or not yet, at least. For the moment, we show that you can just remove all duplicates and then reassign the values you want to keep as records in the table.
This approach is probably more secure as it makes sure 1) you do remove all possible duplicates among the data from the table, and 2) you control which record you want to insert in their place afterwards.
Otherwise, you can use a query of the following structure (which we haven’t applied to our data set simply because the initial data in this table doesn’t contain duplicates), where you delete all records where the hire date coincides but the one where employee number is the highest.

DELETE t1 FROM employees e1
INNER JOIN employees e2
WHERE
  e1.emp_no < e2.emp_no AND
  e1.hire_date = e2.hire_date;

 
Hope this helps.
Best,
Martin