[RESOLVED] Duplicates across all fields

swatisonee

Hi,

I ve been reading all the forum posts on finding duplicates and theyre generally on the first name last name basis.

I however need to find duplicates across the entire structure of a table. That is, every field (total 30) of a record is a duplicate entry of another record except the id field which is set to autoincrement.

Any suggestions please ?

Thanks.
Swati

bpat1434

Do you mean an exact duplicate of all the fields grouped, or individually? Like, having a few overlaps, or an exact duplicate (minus id)?

~Brett

EDIT
Found this in the mySQL manual under SELECT query syntax:

Posted by Johann Eckert on February 11 2004 1:14pm [Delete] [Edit]

To find double entries in a table:

SELECT db1.*
FROM tbl_data db1, tbl_data k2
WHERE db1.id <> db2.id
AND db1.name = db2.name

db1.id must be the PK
db1.name must be the fields that should be verified as double entries.

(I'm not sure wether the code is correct but in my case it works)

Johann

swatisonee

hi brett,

i mean each field being identical:

example : id 1 : a b c d e f g
id 2: a b c d e f g

this situ arises when 2 users add the same data and in my table thats a common occurrence. for future inputs i plan to put a script to disallow this but what do i do for the existing table ?

Swati

bpat1434

So then, you can do this:

SELECT db1.*
FROM table_name as db1, table_name as db2
WHERE db1.id<>db2.id
AND db1.colA=db2.colA
AND db1.colB=db2.colB
AND db1.colC=db2.colC
AND db1.colD=db2.colD
AND db1.colE=db2.colE
--... Continuing until all 30 columns are compared
AND db1.col30=db2.col30

That will select them.

~Brett

swatisonee

Hey thanks ! How do i mark this thread solved ?

bpat1434

Thread Tools --> Mark Resolved

It's in the top right, it's a drop-down menu.

~Brett