0

I have so much duplication of record in my database table. what i want to do is to remove the duplication using query in java. How can i do that?.

Muhammad Haryadi Futra
  • 411
  • 4
  • 9
  • 21
  • What do you have so far? Is there any code to show? What table? How do you determine a record is a duplicate? – dic19 Sep 26 '14 at 11:27
  • Why Java? This is generic database maintenance - better do that using the tooling of the DBMS and restructure your application to guard against duplicate data being introduced in the first place. – Gimby Sep 26 '14 at 11:28
  • Why not a query on your database??? – shinjw Sep 26 '14 at 11:30

2 Answers2

0
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;

Above sql query will delete rows where name field is duplicate and only those unique rows will be retained where name is unique and ID field is lowest. For example rows with ID 5 and 6 will be deleted and rows with 1 and 2 will be retained.

Kannan Thangadurai
  • 1,117
  • 2
  • 17
  • 36
0

To remove duplicates you can use @Kanan's query. To perform this is java just use the following code.

try 
{ 
Statement st = connection.createStatement(); 

String query = "DELETE t1 FROM table t1, table t2 WHERE t1.name = t2.name AND t1.id > t2.id;" 

st.executeUpdate(); 
}
catch(Exception e)
{ 
       e.printStackTrace(); 
}
Vighanesh Gursale
  • 921
  • 5
  • 15
  • 31