select all the duplicate records

[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

[Ken Schaefer]

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

Cheers
Ken

"atse" <dunggaze@yahoo.com> wrote in message
news:no4jb.112560$ko%.54876@news04.bloor.is.net.ca ble.rogers.com...
: 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
:
:

[atse]

Cool, thanks

"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:eOFYCytkDHA.3316@tk2msftngp13.phx.gbl...

> SELECT
> Field1, Field2, Field3
> FROM
> myTable
> GROUP BY
> Field1, Field2, Field3
> HAVING
> Count(*) > 1
>
> Cheers
> Ken
>
> "atse" <dunggaze@yahoo.com> wrote in message
> news:no4jb.112560$ko%.54876@news04.bloor.is.net.ca ble.rogers.com...
> : 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
> :
> :
>
>

[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


"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:eOFYCytkDHA.3316@tk2msftngp13.phx.gbl...

> SELECT
> Field1, Field2, Field3
> FROM
> myTable
> GROUP BY
> Field1, Field2, Field3
> HAVING
> Count(*) > 1
>
> Cheers
> Ken
>
> "atse" <dunggaze@yahoo.com> wrote in message
> news:no4jb.112560$ko%.54876@news04.bloor.is.net.ca ble.rogers.com...
> : 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
> :
> :
>
>

[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


"atse" <dunggaze@yahoo.com> wrote in message
news:CBpjb.349225$Lnr1.181374@news01.bloor.is.net. cable.rogers.com...

> 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
>
>
> "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
> news:eOFYCytkDHA.3316@tk2msftngp13.phx.gbl...

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

[atse]

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


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OrID4T9kDHA.2436@TK2MSFTNGP09.phx.gbl...

> 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
>
>
> "atse" <dunggaze@yahoo.com> wrote in message
> news:CBpjb.349225$Lnr1.181374@news01.bloor.is.net. cable.rogers.com...

> > 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
> >
> >
> > "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
> > news:eOFYCytkDHA.3316@tk2msftngp13.phx.gbl...

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

>
>

[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


atse wrote:

> I am using MySQL. This doesn't work, which complains with
> myTable.qfinddups doesn't exist.
>
>
> "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:OrID4T9kDHA.2436@TK2MSFTNGP09.phx.gbl...

>> 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
>>
>>
>> "atse" <dunggaze@yahoo.com> wrote in message
>> news:CBpjb.349225$Lnr1.181374@news01.bloor.is.net. cable.rogers.com...

>>> 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
>>>
>>>
>>> "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
>>> news:eOFYCytkDHA.3316@tk2msftngp13.phx.gbl...
>>>> SELECT
>>>> Field1, Field2, Field3
>>>> FROM
>>>> myTable
>>>> GROUP BY
>>>> Field1, Field2, Field3
>>>> HAVING
>>>> Count(*) > 1
>>>>
>>>> Cheers
>>>> Ken

[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"



"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:ukoS84JlDHA.2732@TK2MSFTNGP11.phx.gbl...

> 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.
> >
> >
> > "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:OrID4T9kDHA.2436@TK2MSFTNGP09.phx.gbl...

> >> 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
> >>
> >>
> >> "atse" <dunggaze@yahoo.com> wrote in message
> >> news:CBpjb.349225$Lnr1.181374@news01.bloor.is.net. cable.rogers.com...
> >>> 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
> >>>
> >>>
> >>> "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
> >>> news:eOFYCytkDHA.3316@tk2msftngp13.phx.gbl...
> >>>> SELECT
> >>>> Field1, Field2, Field3
> >>>> FROM
> >>>> myTable
> >>>> GROUP BY
> >>>> Field1, Field2, Field3
> >>>> HAVING
> >>>> Count(*) > 1
> >>>>
> >>>> Cheers
> >>>> Ken

>
>
>

[Ken Schaefer]

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

Cheers
Ken

"atse" <dunggaze@yahoo.com> wrote in message
news:JPYjb.367003$Lnr1.53022@news01.bloor.is.net.c able.rogers.com...
: 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"
:
:
:
: "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
: news:ukoS84JlDHA.2732@TK2MSFTNGP11.phx.gbl...
: > 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.
: > >
: > >
: > > "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
: > > news:OrID4T9kDHA.2436@TK2MSFTNGP09.phx.gbl...
: > >> 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
: > >>
: > >>
: > >> "atse" <dunggaze@yahoo.com> wrote in message
: > >> news:CBpjb.349225$Lnr1.181374@news01.bloor.is.net. cable.rogers.com...
: > >>> 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
: > >>>
: > >>>
: > >>> "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
: > >>> news:eOFYCytkDHA.3316@tk2msftngp13.phx.gbl...
: > >>>> SELECT
: > >>>> Field1, Field2, Field3
: > >>>> FROM
: > >>>> myTable
: > >>>> GROUP BY
: > >>>> Field1, Field2, Field3
: > >>>> HAVING
: > >>>> Count(*) > 1
: > >>>>
: > >>>> Cheers
: > >>>> Ken
: >
: >
: >
:
:

[atse]

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

Atse

"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:uabgnhTlDHA.2272@tk2msftngp13.phx.gbl...

> [url]http://www.google.com.au/search?q=finding+duplicate+records+with+mySQL[/url]
>
> Cheers
> Ken
>
> "atse" <dunggaze@yahoo.com> wrote in message
> news:JPYjb.367003$Lnr1.53022@news01.bloor.is.net.c able.rogers.com...
> : 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"
> :
> :
> :
> : "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> : news:ukoS84JlDHA.2732@TK2MSFTNGP11.phx.gbl...
> : > 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.
> : > >
> : > >
> : > > "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> : > > news:OrID4T9kDHA.2436@TK2MSFTNGP09.phx.gbl...
> : > >> 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
> : > >>
> : > >>
> : > >> "atse" <dunggaze@yahoo.com> wrote in message
> : > >>

news:CBpjb.349225$Lnr1.181374@news01.bloor.is.net. cable.rogers.com...

> : > >>> 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
> : > >>>
> : > >>>
> : > >>> "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
> : > >>> news:eOFYCytkDHA.3316@tk2msftngp13.phx.gbl...
> : > >>>> SELECT
> : > >>>> Field1, Field2, Field3
> : > >>>> FROM
> : > >>>> myTable
> : > >>>> GROUP BY
> : > >>>> Field1, Field2, Field3
> : > >>>> HAVING
> : > >>>> Count(*) > 1
> : > >>>>
> : > >>>> Cheers
> : > >>>> Ken
> : >
> : >
> : >
> :
> :
>
>