Remove Duplicate Data From mysql database

Question

To work on database related stuffs. Mostly it is done when client send you its data in form of excel sheets and you push that data to database tables after some excel manipulations. I have also done it many times.

A very common problem faced in this approach is that it might result in duplicate rows at times, because data sent is mostly from departments like HR and finance where people are not well aware of data normalization techniques [:-)].

I will use Employee table where column names are id, name, department and email.

Below are the SQL scripts for generating the test data.

Create schema TestDB;

CREATE TABLE EMPLOYEE
(

   ID INT,
   NAME Varchar(100),
   DEPARTMENT INT,
   EMAIL Varchar(100)
);

INSERT INTO EMPLOYEE VALUES (1,'Anish',101,'anish@howtodoinjava.com');

INSERT INTO EMPLOYEE VALUES (2,'Lokesh',102,'lokesh@howtodoinjava.com');

INSERT INTO EMPLOYEE VALUES (3,'Rakesh',103,'rakesh@howtodoinjava.com');

INSERT INTO EMPLOYEE VALUES (4,'Yogesh',104,'yogesh@howtodoinjava.com');

--These are the duplicate rows

INSERT INTO EMPLOYEE VALUES (5,'Anish',101,'anish@howtodoinjava.com');
INSERT INTO EMPLOYEE VALUES (6,'Lokesh',102,'lokesh@howtodoinjava.com');

Solution:

DELETE e1 FROM EMPLOYEE e1, EMPLOYEE e2 WHERE e1.name = e2.name AND e1.id > e2.id;

You always could make the e-mail column unique. Or even combine the name, id?, e-mail as unique in your database — Ron van der Heijden, Mar 07 '13 at 09:12
No,This is not Question,,,,,, i just share that topic .... and want some alternative raleted to that.... — Pankaj Sharma, Mar 07 '13 at 09:29
i just provide a way to delete duplicate data from table .... it will help u to delete data which r duplicate — Pankaj Sharma, Mar 07 '13 at 09:31

score 0 · Answer 1 · edited May 23 '17 at 10:27

0

delete all duplicate record in excel sheet itself using filter and then insert those record in ur database

use distinct keyword for unique

How to insert Distinct Records from Table A to Table B (both tables have same structure) check this stack overflow link

edited May 23 '17 at 10:27

Community

1
1

answered Mar 07 '13 at 09:12

Francis Stalin

439
1
4
14

score 0 · Answer 2 · edited May 23 '17 at 12:02

0

Add Unique constraint on name field and use below query INSERT INTO EMPLOYEE VALUES Please refer "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

edited May 23 '17 at 12:02

Community

1
1

answered Mar 07 '13 at 09:15

shola

704
4
11

You're assuming that `NAME` is unique. The link you're referring is a good way to solve the problem but you should think carefully about what actually makes the row unique. – JodyT Mar 07 '13 at 10:22
Answer is based on delete query provided in question – shola Mar 07 '13 at 10:42
I missed that part, my apologies. But this was also directed at @Sadia Aziz. It's not uncommon to have employees with the same name. – JodyT Mar 07 '13 at 11:01

score 0 · Answer 3 · answered Mar 07 '13 at 10:30

You can always make sure before inserting to the database either that record already exists or not, in your case you can have condition on its unique key which will be different for every employee. Moreover you can have a single column as unique key or a composite key that uses more than one columns to uniquely identify a record.

Remove Duplicate Data From mysql database

3 Answers3