Deleting duplicate records from table

Question

I want to delete duplicate records from a mysql table. It is executing fine, but it is displaying "no rows affected"?

DELETE n1 
FROM dummyuser1  n1, dummyuser1 n2
WHERE  n1.id > n2.id and  n1.name = n2.name;

The above query is executing but it is displaying "no rows affected".

show your table structure and on which field basis you want to make your data unique. — Zafar Malik, Oct 09 '15 at 09:35
@Maruthi Marripudi: If you want to remove duplicity in name column and want to keep lowest id then your query is fine...if you create a sqlfiddle then someone can check what is the issue... — Zafar Malik, Oct 09 '15 at 09:58
Hi, i created dummyuser1 table in this table i made 2 rows replica with same id,name, salary columns but i dont want to see. eg: first row 1,maruthi,30000 and second row is 2, dummyname,20000 and first row and 2 row all coloumns are repeating twice, i dont want to be like that, i want both replica rows to be unique....... — Maruthi Marripudi, Oct 09 '15 at 11:51

score 0 · Answer 1 · edited Oct 09 '15 at 11:23

0

The simplest way is just add a unique index that will delete all duplicate rows like:

alter ignore table dummyuser  
add unique index idx_uk_dummyuser (name);

And it help you in the future avoid the duplicates in your table

edited Oct 09 '15 at 11:23

Maruthi Marripudi

answered Oct 09 '15 at 09:15

Roman Marusyk

Adding a unique index won't delete duplicate entries. It will *fail* if there are duplicates. – user207421 Oct 09 '15 at 09:31
@EJP As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and its use produces an error. – Roman Marusyk Oct 09 '15 at 09:39
@EJP And I found the similar answer http://stackoverflow.com/a/3312066/4275342 / My table type is MyISAM and its works – Roman Marusyk Oct 09 '15 at 09:42
1

@EJP: As MegaTron is using alter ignore syntax, so it will automatically remove duplicate records and keep only first record and will create unique index. My +1 for the answer. – Zafar Malik Oct 09 '15 at 09:47

1 Answers1