I have a database of which the following are the main tables: client, cards, dependants and main address. The database has about 400,000 records, and I want to find any duplicate clients that the database has. Client has forename, surname and date of birth, and I want to search on these fields, but I don't know how to start the loop or maybe subquery. I have no problem with joins.
The following statement should show you duplicate rows. If you want to delete the records, just change the SELECT * to DELETE.
SELECT * FROM mytable t WHERE t.rowid > (SELECT MIN(t1.rowid) FROM mytable t1 WHERE t.forename=t1.forename AND t.surname=t1.surname AND t.dob=t1.dob) ;
For More Information
- What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.