[ajug-members] Deleting Duplicate Records using JAVA and JDBC!

[Sizhnor]

Best solution is batch few thousands records to be clean at a time repeat until whole table is clean! Delete duplicate records and keep unique! This can be done easily!
Sent from my BlackBerry® wireless handheld  

-----Original Message-----
From: hsaid at checkfree.com
Date: Tue, 12 Dec 2006 19:04:43 
To:mgm at meritonlinesystems.com,       "General AJUG membership forum \(100-200 messages/month\)"<ajug-members at ajug.org>
Cc:Ajug-Members <ajug-members at www.ajug.org>, ajug-members-bounces at ajug.org
Subject: [ajug-members] Deleting Duplicate Records using JAVA and JDBC!

Members,

I have the following problem:
A table with huge amounts of data running into hundreds of millions. The 
table has the following columns:

RESPONSE_ID  (PK)
RESPONSE_DATA
RECORD_ID

I need to remove all the records with duplicate RECORD_ID, leaving only 
one. 

1- One solution is to get the resulset , and then use the set to add 
records and if not able to add if(!set.add()) remove the record from the 
database. That would involve a lot of DB operations
2- Another option is to put all duplicate records in a list and remove all 
of them in a single query using RESPONSE_ID PRMARY KEY. This would mean a 
a lot of soaking up of RAM by the list with potentially millions of 
records.

3- Option is to use the something like this query to delete multiple 
records:
 
delete from RESPONSE_DATA m1
where m1.rowid > (select min(m2.rowid) from RESPONSE_DATA m2 where m1.
RECORD_ID=m2.RECORD_ID
);


Thanks a bunch for your help,
Hammond S.
 

_______________________________________________
ajug-members mailing list
ajug-members at ajug.org
http://www.ajug.org/mailman/listinfo/ajug-members