Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database full of records of people with simple information like first name, last name, email, location, ... . I need to eliminate the duplicate records. As I've search the process is called "duplicate elimination in Data Cleansing". Does anyone know a good open source tool to do that?

share|improve this question
    
What RDBMS ? Is it using TSQL or PL/SQL ? –  Kin May 28 '13 at 2:21

2 Answers 2

Using T-SQL & PL/SQL, you can use ROW_NUMBER() OVER (PARTITION BY) to find and delete duplicate records in a Table.

Refer to some best examples Removing Duplicates from a Table in SQL Server and How can I remove duplicate rows? as well as Oracle 'Partition By' and 'Row_Number' keyword

share|improve this answer
    
The thing is my duplicate records are not exactly the same. two records may differ in one field or more. –  AliBZ May 28 '13 at 16:47
    
@AliBZ In that case, I would recommend you highlighting the duplicate records (selecting) and then asking your Business owners to decide it. If you refer to the links that I have mentioned, then it has all scenarios of finding duplicate records. Also, if the same record differs in one or more field then how is it a Duplicate ? Post create table statement with some data and your expected results to help you better –  Kin May 28 '13 at 16:58

For PostgreSQL you can do so with a "simple" SQL statement which may apply to other SQL databases with little or no tweaks:

http://wiki.postgresql.org/wiki/Deleting_duplicates

Note that it deletes all entries that have a higher ID, better to know that...

DELETE FROM tablename
WHERE id IN (SELECT id
  FROM (SELECT id,
         row_number() over (partition BY column1, column2, column3 ORDER BY id) AS rnum
         FROM tablename) t
  WHERE t.rnum > 1);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.