select all the duplicate records

A

atse

Hi,

My table in the database may contain duplicate records, which means except
the primary key (auto_increment) field is unique, all or almost of all the
fields are with the same content. How can I select them to display and
delete them?
Thanks for any idea.

Atse
 
K

Ken Schaefer

SELECT
Field1, Field2, Field3
FROM
myTable
GROUP BY
Field1, Field2, Field3
HAVING
Count(*) > 1

Cheers
Ken

: Hi,
:
: My table in the database may contain duplicate records, which means except
: the primary key (auto_increment) field is unique, all or almost of all the
: fields are with the same content. How can I select them to display and
: delete them?
: Thanks for any idea.
:
: Atse
:
:
 
A

atse

This lists all the duplicates only with one of each. If the record has more
than one duplicate, it can't show all of them but one only. Is there a way
to show all? Thanks again.

Atse
 
B

Bob Barrows

Is this Access? I suggest using Ken's SQL to create a saved query called
qFindDups. Then create another query using this SQL:

Select t.* FROM myTable t INNER JOIN qFindDups q
ON t.Field1=q.Field1 AND t.Field2=q.Field2 AND t.Field3=q.Field3

HTH,
Bob Barrows
 
A

atse

I am using MySQL. This doesn't work, which complains with myTable.qfinddups
doesn't exist.
 
B

Bob Barrows

Does MySQL allow subqueries in the FROM clause? If so, do this:

Select t.* FROM myTable t INNER JOIN
(
SELECT
Field1, Field2, Field3
FROM
myTable
GROUP BY
Field1, Field2, Field3
HAVING
Count(*) > 1
) q
ON t.Field1=q.Field1 AND t.Field2=q.Field2 AND t.Field3=q.Field3

Please let us know upfront what database you are using so we don't waste our
time and yours giving you irrelevant solutions.

Bob Barrows
 
A

atse

Sorry, I will complete my question next time.
I guess MySQL may not support substring? Because it complains with that:


Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

[MySQL][ODBC 3.51 Driver][mysqld-4.0.5-beta-max-nt]You have an error in your
SQL syntax. Check the manual that corresponds to your MySQL server version
for the right syntax to use near 'SELECT f1, f3(

/dp_record.asp, line 37



Here is the line37 (please ignore the broken lines):
' file is myTable, and f1, f3 ... are the field names

dataSQL = "select t.* FROM file t INNER JOIN (SELECT f1, f3, f5 FROM file
GROUP BY f1, f3, f5 "
dataSQL = dataSQL & "HAVING Count(*) > 1) q ON t.f1=q.f1 AND t.f3=q.f3 AND
t.f5=q.f5"
 
K

Ken Schaefer

http://www.google.com.au/search?q=finding+duplicate+records+with+mySQL

Cheers
Ken

: Sorry, I will complete my question next time.
: I guess MySQL may not support substring? Because it complains with that:
:
:
: Microsoft OLE DB Provider for ODBC Drivers error '80040e09'
:
: [MySQL][ODBC 3.51 Driver][mysqld-4.0.5-beta-max-nt]You have an error in
your
: SQL syntax. Check the manual that corresponds to your MySQL server version
: for the right syntax to use near 'SELECT f1, f3(
:
: /dp_record.asp, line 37
:
:
:
: Here is the line37 (please ignore the broken lines):
: ' file is myTable, and f1, f3 ... are the field names
:
: dataSQL = "select t.* FROM file t INNER JOIN (SELECT f1, f3, f5 FROM file
: GROUP BY f1, f3, f5 "
: dataSQL = dataSQL & "HAVING Count(*) > 1) q ON t.f1=q.f1 AND t.f3=q.f3 AND
: t.f5=q.f5"
:
:
:
: : > Does MySQL allow subqueries in the FROM clause? If so, do this:
: >
: > Select t.* FROM myTable t INNER JOIN
: > (
: > SELECT
: > Field1, Field2, Field3
: > FROM
: > myTable
: > GROUP BY
: > Field1, Field2, Field3
: > HAVING
: > Count(*) > 1
: > ) q
: > ON t.Field1=q.Field1 AND t.Field2=q.Field2 AND t.Field3=q.Field3
: >
: > Please let us know upfront what database you are using so we don't waste
: our
: > time and yours giving you irrelevant solutions.
: >
: > Bob Barrows
: >
: >
: > atse wrote:
: > > I am using MySQL. This doesn't work, which complains with
: > > myTable.qfinddups doesn't exist.
: > >
: > >
: > > : > >> Is this Access? I suggest using Ken's SQL to create a saved query
: > >> called qFindDups. Then create another query using this SQL:
: > >>
: > >> Select t.* FROM myTable t INNER JOIN qFindDups q
: > >> ON t.Field1=q.Field1 AND t.Field2=q.Field2 AND t.Field3=q.Field3
: > >>
: > >> HTH,
: > >> Bob Barrows
: > >>
: > >>
: > >> : > >>> This lists all the duplicates only with one of each. If the record
: > >>> has more than one duplicate, it can't show all of them but one
: > >>> only. Is there a way to show all? Thanks again.
: > >>>
: > >>> Atse
: > >>>
: > >>>
: > >>> : > >>>> SELECT
: > >>>> Field1, Field2, Field3
: > >>>> FROM
: > >>>> myTable
: > >>>> GROUP BY
: > >>>> Field1, Field2, Field3
: > >>>> HAVING
: > >>>> Count(*) > 1
: > >>>>
: > >>>> Cheers
: > >>>> Ken
: >
: >
: >
:
:
 
A

atse

It seems there no effective way on that page. Did I miss one of them? Please
point out, Thanks

Atse

Ken Schaefer said:
http://www.google.com.au/search?q=finding+duplicate+records+with+mySQL

Cheers
Ken

: Sorry, I will complete my question next time.
: I guess MySQL may not support substring? Because it complains with that:
:
:
: Microsoft OLE DB Provider for ODBC Drivers error '80040e09'
:
: [MySQL][ODBC 3.51 Driver][mysqld-4.0.5-beta-max-nt]You have an error in
your
: SQL syntax. Check the manual that corresponds to your MySQL server version
: for the right syntax to use near 'SELECT f1, f3(
:
: /dp_record.asp, line 37
:
:
:
: Here is the line37 (please ignore the broken lines):
: ' file is myTable, and f1, f3 ... are the field names
:
: dataSQL = "select t.* FROM file t INNER JOIN (SELECT f1, f3, f5 FROM file
: GROUP BY f1, f3, f5 "
: dataSQL = dataSQL & "HAVING Count(*) > 1) q ON t.f1=q.f1 AND t.f3=q.f3 AND
: t.f5=q.f5"
:
:
:
: : > Does MySQL allow subqueries in the FROM clause? If so, do this:
: >
: > Select t.* FROM myTable t INNER JOIN
: > (
: > SELECT
: > Field1, Field2, Field3
: > FROM
: > myTable
: > GROUP BY
: > Field1, Field2, Field3
: > HAVING
: > Count(*) > 1
: > ) q
: > ON t.Field1=q.Field1 AND t.Field2=q.Field2 AND t.Field3=q.Field3
: >
: > Please let us know upfront what database you are using so we don't waste
: our
: > time and yours giving you irrelevant solutions.
: >
: > Bob Barrows
: >
: >
: > atse wrote:
: > > I am using MySQL. This doesn't work, which complains with
: > > myTable.qfinddups doesn't exist.
: > >
: > >
: > > : > >> Is this Access? I suggest using Ken's SQL to create a saved query
: > >> called qFindDups. Then create another query using this SQL:
: > >>
: > >> Select t.* FROM myTable t INNER JOIN qFindDups q
: > >> ON t.Field1=q.Field1 AND t.Field2=q.Field2 AND t.Field3=q.Field3
: > >>
: > >> HTH,
: > >> Bob Barrows
: > >>
: > >>
: > >> : > >>> This lists all the duplicates only with one of each. If the record
: > >>> has more than one duplicate, it can't show all of them but one
: > >>> only. Is there a way to show all? Thanks again.
: > >>>
: > >>> Atse
: > >>>
: > >>>
: > >>> : > >>>> SELECT
: > >>>> Field1, Field2, Field3
: > >>>> FROM
: > >>>> myTable
: > >>>> GROUP BY
: > >>>> Field1, Field2, Field3
: > >>>> HAVING
: > >>>> Count(*) > 1
: > >>>>
: > >>>> Cheers
: > >>>> Ken
: >
: >
: >
:
:
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with a login script, SESSION user rights and put this together so it works with the other pages and MySQL. Code examples. 2
'Needless flexibilities' and structured records [very long] 10
Hi, I am a webflow user. I am looking for CSS code that can KEEP ALL ELEMENTS POSITIONED in the SAME spot across all resolutions 0
Finding duplicate records before creating using FasterCSV 11
[RFC] Mysql::DBLink 3
How to change star rating color on mouseenter, on mouseout, and on onclick 0
[SUMMARY] Records and Arrays (#170) 0
Display DB Records Differently 4

Members online

No members online now.
Total: 27 (members: 0, guests: 27)
Robots: 82

Forum statistics

Threads
473,754
Messages
2,569,526
Members
44,997
Latest member
mileyka

Latest Threads

Top