I want to delete duplicate rows from a MySQL table

Asked Apr 04 '19 at 03:45

Active Apr 04 '19 at 06:38

Viewed 51 times

I want to delete duplicate rows from this MySQL table without creating a temp table. I tried to do it "WITH CTE" but that doesn't work as I am using MySQL-Workbench.

Please click here to view the table:

I tried to achieve the result using this query but it's not working.

DELETE FROM employee WHERE 
(SELECT * FROM
   (SELECT row_number() OVER (partition by id) rn FROM employee) 
 alias) > 1;

I want to delete all duplicate rows but keep one.

edited Apr 04 '19 at 06:27

Mike Lischke

36,881
12
88
141

asked Apr 04 '19 at 03:45

Prãshant Saraswat

Possible duplicate of [Remove duplicate rows in MySQL](https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) – weegee Apr 04 '19 at 07:30
Alsi if you don't want to keep duplicate values, just [make the column UNIQUE](https://stackoverflow.com/questions/5038040/mysql-make-an-existing-field-unique) – weegee Apr 04 '19 at 07:31
@Prãshant Saraswat do you want to keep one of them? – Arjun Singh Apr 04 '19 at 08:07
Yes, I want to keep one of the duplicate records. – Prãshant Saraswat Apr 04 '19 at 08:21

I want to delete duplicate rows from a MySQL table

0 Answers0