Find Duplicate Entries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Find Duplicate Entries

Post by icesolid »

Is there any SQL command I can run to search a MySQL DB and find any duplicate entries and then print out which ones are duplicate?
User avatar
kaszu
Forum Regular
Posts: 749
Joined: Wed Jul 19, 2006 7:29 am

Post by kaszu »

Maybe there is a better solution, but this is how i would solve it for table where fields are "id" (UNIQUE), "title", "body"

Code: Select all

SELECT a.* FROM articles as a, articles as a2 where a.title = a2.title and a.body = a2.body and a.id <> a2.id
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

Just use count() and group by the unique field and use the having clause to filter down to records with count >= 2.. somthing like this:

Code: Select all

select uniqueCol, count(*) as count from tbl_name group by uniqueCol having count >= 2
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post by icesolid »

Excellent!
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

Just interested to know how to find duplicate record of more than one fields on the table using count function ..Does anybody knows that ? ;)
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

Should be something like:

Code: Select all

select uniqueCol0, uniqueCol1, uniqueCol2, count(*) as count from tbl_name group by uniqueCol, uniqueCol, uniqueCol having count >= 2
[/quote]
Post Reply