Professional Web Applications Themes

select all the duplicate records - ASP

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...

  1. #1

    Default select all the duplicate records

    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 Guest

  2. #2

    Default Re: select all the duplicate records

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

    Cheers
    Ken

    "atse" <dunggaze> wrote in message
    news:no4jb.112560$ko%.54876news04.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
    :
    :


    Ken Schaefer Guest

  3. #3

    Default Re: select all the duplicate records

    Cool, thanks

    "Ken Schaefer" <kenREMOVETHISadOpenStatic.com> wrote in message
    news:eOFYCytkDHA.3316tk2msftngp13.phx.gbl...
    > SELECT
    > Field1, Field2, Field3
    > FROM
    > myTable
    > GROUP BY
    > Field1, Field2, Field3
    > HAVING
    > Count(*) > 1
    >
    > Cheers
    > Ken
    >
    > "atse" <dunggaze> wrote in message
    > news:no4jb.112560$ko%.54876news04.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 Guest

  4. #4

    Default Re: select all the duplicate records

    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" <kenREMOVETHISadOpenStatic.com> wrote in message
    news:eOFYCytkDHA.3316tk2msftngp13.phx.gbl...
    > SELECT
    > Field1, Field2, Field3
    > FROM
    > myTable
    > GROUP BY
    > Field1, Field2, Field3
    > HAVING
    > Count(*) > 1
    >
    > Cheers
    > Ken
    >
    > "atse" <dunggaze> wrote in message
    > news:no4jb.112560$ko%.54876news04.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 Guest

  5. #5

    Default Re: select all the duplicate records

    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> wrote in message
    news:CBpjb.349225$Lnr1.181374news01.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" <kenREMOVETHISadOpenStatic.com> wrote in message
    > news:eOFYCytkDHA.3316tk2msftngp13.phx.gbl...
    > > SELECT
    > > Field1, Field2, Field3
    > > FROM
    > > myTable
    > > GROUP BY
    > > Field1, Field2, Field3
    > > HAVING
    > > Count(*) > 1
    > >
    > > Cheers
    > > Ken

    Bob Barrows Guest

  6. #6

    Default Re: select all the duplicate records

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


    "Bob Barrows" <reb01501NOyahoo.SPAMcom> wrote in message
    news:OrID4T9kDHA.2436TK2MSFTNGP09.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> wrote in message
    > news:CBpjb.349225$Lnr1.181374news01.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" <kenREMOVETHISadOpenStatic.com> wrote in message
    > > news:eOFYCytkDHA.3316tk2msftngp13.phx.gbl...
    > > > SELECT
    > > > Field1, Field2, Field3
    > > > FROM
    > > > myTable
    > > > GROUP BY
    > > > Field1, Field2, Field3
    > > > HAVING
    > > > Count(*) > 1
    > > >
    > > > Cheers
    > > > Ken
    >
    >

    atse Guest

  7. #7

    Default Re: select all the duplicate records

    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" <reb01501NOyahoo.SPAMcom> wrote in message
    > news:OrID4T9kDHA.2436TK2MSFTNGP09.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> wrote in message
    >> news:CBpjb.349225$Lnr1.181374news01.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" <kenREMOVETHISadOpenStatic.com> wrote in message
    >>> news:eOFYCytkDHA.3316tk2msftngp13.phx.gbl...
    >>>> SELECT
    >>>> Field1, Field2, Field3
    >>>> FROM
    >>>> myTable
    >>>> GROUP BY
    >>>> Field1, Field2, Field3
    >>>> HAVING
    >>>> Count(*) > 1
    >>>>
    >>>> Cheers
    >>>> Ken


    Bob Barrows Guest

  8. #8

    Default Re: select all the duplicate records

    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" <reb01501NOyahoo.SPAMcom> wrote in message
    news:ukoS84JlDHA.2732TK2MSFTNGP11.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" <reb01501NOyahoo.SPAMcom> wrote in message
    > > news:OrID4T9kDHA.2436TK2MSFTNGP09.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> wrote in message
    > >> news:CBpjb.349225$Lnr1.181374news01.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" <kenREMOVETHISadOpenStatic.com> wrote in message
    > >>> news:eOFYCytkDHA.3316tk2msftngp13.phx.gbl...
    > >>>> SELECT
    > >>>> Field1, Field2, Field3
    > >>>> FROM
    > >>>> myTable
    > >>>> GROUP BY
    > >>>> Field1, Field2, Field3
    > >>>> HAVING
    > >>>> Count(*) > 1
    > >>>>
    > >>>> Cheers
    > >>>> Ken
    >
    >
    >

    atse Guest

  9. #9

    Default Re: select all the duplicate records

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

    Cheers
    Ken

    "atse" <dunggaze> wrote in message
    news:JPYjb.367003$Lnr1.53022news01.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" <reb01501NOyahoo.SPAMcom> wrote in message
    : news:ukoS84JlDHA.2732TK2MSFTNGP11.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" <reb01501NOyahoo.SPAMcom> wrote in message
    : > > news:OrID4T9kDHA.2436TK2MSFTNGP09.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> wrote in message
    : > >> news:CBpjb.349225$Lnr1.181374news01.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" <kenREMOVETHISadOpenStatic.com> wrote in message
    : > >>> news:eOFYCytkDHA.3316tk2msftngp13.phx.gbl...
    : > >>>> SELECT
    : > >>>> Field1, Field2, Field3
    : > >>>> FROM
    : > >>>> myTable
    : > >>>> GROUP BY
    : > >>>> Field1, Field2, Field3
    : > >>>> HAVING
    : > >>>> Count(*) > 1
    : > >>>>
    : > >>>> Cheers
    : > >>>> Ken
    : >
    : >
    : >
    :
    :


    Ken Schaefer Guest

  10. #10

    Default Re: select all the duplicate records

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

    Atse

    "Ken Schaefer" <kenREMOVETHISadOpenStatic.com> wrote in message
    news:uabgnhTlDHA.2272tk2msftngp13.phx.gbl...
    > [url]http://www.google.com.au/search?q=finding+duplicate+records+with+mySQL[/url]
    >
    > Cheers
    > Ken
    >
    > "atse" <dunggaze> wrote in message
    > news:JPYjb.367003$Lnr1.53022news01.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" <reb01501NOyahoo.SPAMcom> wrote in message
    > : news:ukoS84JlDHA.2732TK2MSFTNGP11.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" <reb01501NOyahoo.SPAMcom> wrote in message
    > : > > news:OrID4T9kDHA.2436TK2MSFTNGP09.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> wrote in message
    > : > >>
    news:CBpjb.349225$Lnr1.181374news01.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" <kenREMOVETHISadOpenStatic.com> wrote in message
    > : > >>> news:eOFYCytkDHA.3316tk2msftngp13.phx.gbl...
    > : > >>>> SELECT
    > : > >>>> Field1, Field2, Field3
    > : > >>>> FROM
    > : > >>>> myTable
    > : > >>>> GROUP BY
    > : > >>>> Field1, Field2, Field3
    > : > >>>> HAVING
    > : > >>>> Count(*) > 1
    > : > >>>>
    > : > >>>> Cheers
    > : > >>>> Ken
    > : >
    > : >
    > : >
    > :
    > :
    >
    >

    atse Guest

Similar Threads

  1. Duplicate records
    By DuLaus in forum Coldfusion - Advanced Techniques
    Replies: 7
    Last Post: October 25th, 03:20 PM
  2. Return Records and not duplicate them :: Again
    By The Ox in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 16th, 05:57 PM
  3. Deleting duplicate records
    By Dinesh.T.K in forum FileMaker
    Replies: 3
    Last Post: August 13th, 12:00 AM
  4. Need help serializing duplicate records
    By Scott in forum FileMaker
    Replies: 2
    Last Post: July 30th, 06:24 PM
  5. Duplicate records?
    By Bridget Eley in forum FileMaker
    Replies: 2
    Last Post: July 28th, 12:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139