How can I delete a duplicated row from a table? I want to keep one record and delete whichever are duplicated.
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
e1.emp_no < e2.emp_no AND
e1.hire_date = e2.hire_date;
Hope this helps.