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" <du******@yahoo.com> wrote in message
news:no********************@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" <ke*******@THISadOpenStatic.com> wrote in message
news:eO**************@tk2msftngp13.phx.gbl...

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

Cheers
Ken

"atse" <du******@yahoo.com> wrote in message
news:no********************@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" <ke*******@THISadOpenStatic.com> wrote in message
news:eO**************@tk2msftngp13.phx.gbl...

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

Cheers
Ken

"atse" <du******@yahoo.com> wrote in message
news:no********************@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" <du******@yahoo.com> wrote in message
news:CB**********************@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" <ke*******@THISadOpenStatic.com> wrote in message
news:eO**************@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" <re******@NOyahoo.SPAMcom> wrote in message
news:Or**************@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" <du******@yahoo.com> wrote in message
news:CB**********************@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" <ke*******@THISadOpenStatic.com> wrote in message
news:eO**************@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" <re******@NOyahoo.SPAMcom> wrote in message
news:Or**************@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" <du******@yahoo.com> wrote in message
news:CB**********************@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" <ke*******@THISadOpenStatic.com> wrote in message
news:eO**************@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" <re******@NOyahoo.SPAMcom> wrote in message
news:uk**************@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" <re******@NOyahoo.SPAMcom> wrote in message
news:Or**************@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" <du******@yahoo.com> wrote in message
news:CB**********************@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" <ke*******@THISadOpenStatic.com> wrote in message
news:eO**************@tk2msftngp13.phx.gbl...
> SELECT
> Field1, Field2, Field3
> FROM
> myTable
> GROUP BY
> Field1, Field2, Field3
> HAVING
> Count(*) > 1
>
> Cheers
> Ken

[Ken Schaefer]

http://www.google.com.au/search?q=fi...rds+with+mySQL

Cheers
Ken

"atse" <du******@yahoo.com> wrote in message
news:JP*********************@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" <re******@NOyahoo.SPAMcom> wrote in message
: news:uk**************@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" <re******@NOyahoo.SPAMcom> wrote in message
: > > news:Or**************@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" <du******@yahoo.com> wrote in message
: > >> news:CB**********************@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" <ke*******@THISadOpenStatic.com> wrote in message
: > >>> news:eO**************@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" <ke*******@THISadOpenStatic.com> wrote in message
news:ua**************@tk2msftngp13.phx.gbl...

http://www.google.com.au/search?q=fi...rds+with+mySQL

Cheers
Ken

"atse" <du******@yahoo.com> wrote in message
news:JP*********************@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" <re******@NOyahoo.SPAMcom> wrote in message
: news:uk**************@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" <re******@NOyahoo.SPAMcom> wrote in message
: > > news:Or**************@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" <du******@yahoo.com> wrote in message
: > >> news:CB**********************@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" <ke*******@THISadOpenStatic.com> wrote in message
: > >>> news:eO**************@tk2msftngp13.phx.gbl...
: > >>>> SELECT
: > >>>> Field1, Field2, Field3
: > >>>> FROM
: > >>>> myTable
: > >>>> GROUP BY
: > >>>> Field1, Field2, Field3
: > >>>> HAVING
: > >>>> Count(*) > 1
: > >>>>
: > >>>> Cheers
: > >>>> Ken
: >
: >
: >
:
: