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

[hsaid at checkfree.com]

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.