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

closed as off-topic by Max Vernon, Paul White, RLF, Mark Storey-Smith, RolandoMySQLDBA Oct 15 at 21:15

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – Max Vernon, Paul White, RLF, Mark Storey-Smith, RolandoMySQLDBA
If this question can be reworded to fit the rules in the help center, please edit the question.

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

3 Answers 3

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

The tool we use to remove duplicate records in our database (Access) is WinPure Clean & Match, I think also they do an API for fuzzy matching.

share|improve this answer

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