select all the duplicate records

Discussion in 'ASP General' started by atse, Oct 15, 2003.

  1. atse

    atse Guest

    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, Oct 15, 2003
    #1
    1. Advertising

  2. atse

    Ken Schaefer Guest

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

    Cheers
    Ken

    "atse" <> wrote in message
    news:no4jb.112560$ko%...
    : 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, Oct 15, 2003
    #2
    1. Advertising

  3. atse

    atse Guest

    Cool, thanks

    "Ken Schaefer" <> wrote in message
    news:...
    > SELECT
    > Field1, Field2, Field3
    > FROM
    > myTable
    > GROUP BY
    > Field1, Field2, Field3
    > HAVING
    > Count(*) > 1
    >
    > Cheers
    > Ken
    >
    > "atse" <> wrote in message
    > news:no4jb.112560$ko%...
    > : 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, Oct 15, 2003
    #3
  4. atse

    atse Guest

    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" <> wrote in message
    news:...
    > SELECT
    > Field1, Field2, Field3
    > FROM
    > myTable
    > GROUP BY
    > Field1, Field2, Field3
    > HAVING
    > Count(*) > 1
    >
    > Cheers
    > Ken
    >
    > "atse" <> wrote in message
    > news:no4jb.112560$ko%...
    > : 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, Oct 16, 2003
    #4
  5. atse

    Bob Barrows Guest

    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" <> wrote in message
    news:CBpjb.349225$...
    > 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" <> wrote in message
    > news:...
    > > SELECT
    > > Field1, Field2, Field3
    > > FROM
    > > myTable
    > > GROUP BY
    > > Field1, Field2, Field3
    > > HAVING
    > > Count(*) > 1
    > >
    > > Cheers
    > > Ken
     
    Bob Barrows, Oct 16, 2003
    #5
  6. atse

    atse Guest

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


    "Bob Barrows" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:CBpjb.349225$...
    > > 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" <> wrote in message
    > > news:...
    > > > SELECT
    > > > Field1, Field2, Field3
    > > > FROM
    > > > myTable
    > > > GROUP BY
    > > > Field1, Field2, Field3
    > > > HAVING
    > > > Count(*) > 1
    > > >
    > > > Cheers
    > > > Ken

    >
    >
     
    atse, Oct 17, 2003
    #6
  7. atse

    Bob Barrows Guest

    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" <> wrote in message
    > news:...
    >> 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" <> wrote in message
    >> news:CBpjb.349225$...
    >>> 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" <> wrote in message
    >>> news:...
    >>>> SELECT
    >>>> Field1, Field2, Field3
    >>>> FROM
    >>>> myTable
    >>>> GROUP BY
    >>>> Field1, Field2, Field3
    >>>> HAVING
    >>>> Count(*) > 1
    >>>>
    >>>> Cheers
    >>>> Ken
     
    Bob Barrows, Oct 17, 2003
    #7
  8. atse

    atse Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > > news:...
    > >> 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" <> wrote in message
    > >> news:CBpjb.349225$...
    > >>> 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" <> wrote in message
    > >>> news:...
    > >>>> SELECT
    > >>>> Field1, Field2, Field3
    > >>>> FROM
    > >>>> myTable
    > >>>> GROUP BY
    > >>>> Field1, Field2, Field3
    > >>>> HAVING
    > >>>> Count(*) > 1
    > >>>>
    > >>>> Cheers
    > >>>> Ken

    >
    >
    >
     
    atse, Oct 17, 2003
    #8
  9. atse

    Ken Schaefer Guest

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

    Cheers
    Ken

    "atse" <> wrote in message
    news:JPYjb.367003$...
    : 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" <> wrote in message
    : news:...
    : > 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" <> wrote in message
    : > > news:...
    : > >> 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" <> wrote in message
    : > >> news:CBpjb.349225$...
    : > >>> 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" <> wrote in message
    : > >>> news:...
    : > >>>> SELECT
    : > >>>> Field1, Field2, Field3
    : > >>>> FROM
    : > >>>> myTable
    : > >>>> GROUP BY
    : > >>>> Field1, Field2, Field3
    : > >>>> HAVING
    : > >>>> Count(*) > 1
    : > >>>>
    : > >>>> Cheers
    : > >>>> Ken
    : >
    : >
    : >
    :
    :
     
    Ken Schaefer, Oct 18, 2003
    #9
  10. atse

    atse Guest

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

    Atse

    "Ken Schaefer" <> wrote in message
    news:...
    > http://www.google.com.au/search?q=finding duplicate records with mySQL
    >
    > Cheers
    > Ken
    >
    > "atse" <> wrote in message
    > news:JPYjb.367003$...
    > : 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" <> wrote in message
    > : news:...
    > : > 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" <> wrote in message
    > : > > news:...
    > : > >> 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" <> wrote in message
    > : > >>

    news:CBpjb.349225$...
    > : > >>> 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" <> wrote in message
    > : > >>> news:...
    > : > >>>> SELECT
    > : > >>>> Field1, Field2, Field3
    > : > >>>> FROM
    > : > >>>> myTable
    > : > >>>> GROUP BY
    > : > >>>> Field1, Field2, Field3
    > : > >>>> HAVING
    > : > >>>> Count(*) > 1
    > : > >>>>
    > : > >>>> Cheers
    > : > >>>> Ken
    > : >
    > : >
    > : >
    > :
    > :
    >
    >
     
    atse, Oct 19, 2003
    #10
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. sumit
    Replies:
    1
    Views:
    6,465
    Anatoly
    Nov 25, 2003
  2. Jameel
    Replies:
    1
    Views:
    573
  3. Bas
    Replies:
    2
    Views:
    255
  4. Matt
    Replies:
    2
    Views:
    215
    Roland Hall
    Jan 11, 2004
  5. Replies:
    3
    Views:
    678
    Anthony Jones
    Nov 2, 2006
Loading...

Share This Page