Professional Web Applications Themes

Deleting duplicate entries from database table - MySQL

NOTE: The same message is also posted at comp.lang.php, mailing.database.mysql, mysql on July 7. The scenario is as: 1. A table is created 2. No Primary Key, Unique Key, Validation process. 3. Various duplicate entries gor inserted in the table (all fields contain same data) May be programming logic bug/not handled the case. In the edit process, nothing is edited, (all data are same), and instead of updating the record, new row get inserted. 4. Now it is desired that all the entries with duplicate entries gets deleted abd only 1 record (within duplicate entry) remains in the table. As ...

  1. #1

    Default Deleting duplicate entries from database table


    NOTE: The same message is also posted at comp.lang.php,
    mailing.database.mysql, mysql on July 7.


    The scenario is as:
    1. A table is created
    2. No Primary Key, Unique Key, Validation process.
    3. Various duplicate entries gor inserted in the table (all fields
    contain same data)

    May be programming logic bug/not handled the case.

    In the edit process, nothing is edited, (all data are same), and
    instead of updating the record, new row get inserted.

    4. Now it is desired that all the entries with duplicate entries gets
    deleted abd only 1 record (within duplicate entry) remains in the
    table.

    As in shown in example.

    Suppose a table "address" contains the following records

    -------------------------------------------------------
    | name | address | phone |
    -------------------------------------------------------
    | mr x | 8th lane | 124364 |
    | mr x | 6th lane | 435783 |
    | mrs x | 6th lane | 435783 |
    | mr x | 8th lane | 124364 |
    -------------------------------------------------------
    >> Execute single query (MySQL Version: No Restriction), with sub-query or some other method
    >> After executing the query
    -------------------------------------------------------
    | name | address | phone |
    -------------------------------------------------------
    | mr x | 8th lane | 124364 |
    | mr x | 6th lane | 435783 |
    | mrs x | 6th lane | 435783 |
    -------------------------------------------------------

    Here instead of deleting both duplicate entry

    | mr x | 8th lane | 124364 |

    only one gets deleted (out of two) and no duplicate entries are there
    in table.

    Hope it will clarify my question.

    Thanks.

    Manish

    Manish Guest

  2. #2

    Default Re: Deleting duplicate entries from database table

    Manish wrote:
    > NOTE: The same message is also posted at comp.lang.php,
    > mailing.database.mysql, mysql on July 7.
    >
    >
    > The scenario is as:
    > 1. A table is created
    > 2. No Primary Key, Unique Key, Validation process.
    > 3. Various duplicate entries gor inserted in the table (all fields
    > contain same data)
    >
    > May be programming logic bug/not handled the case.
    >
    > In the edit process, nothing is edited, (all data are same), and
    > instead of updating the record, new row get inserted.
    >
    > 4. Now it is desired that all the entries with duplicate entries gets
    > deleted abd only 1 record (within duplicate entry) remains in the
    > table.
    >
    > As in shown in example.
    >
    > Suppose a table "address" contains the following records
    >
    > -------------------------------------------------------
    > | name | address | phone |
    > -------------------------------------------------------
    > | mr x | 8th lane | 124364 |
    > | mr x | 6th lane | 435783 |
    > | mrs x | 6th lane | 435783 |
    > | mr x | 8th lane | 124364 |
    > -------------------------------------------------------
    >
    >
    >>>Execute single query (MySQL Version: No Restriction), with sub-query or some other method
    >>>After executing the query
    >
    >
    > -------------------------------------------------------
    > | name | address | phone |
    > -------------------------------------------------------
    > | mr x | 8th lane | 124364 |
    > | mr x | 6th lane | 435783 |
    > | mrs x | 6th lane | 435783 |
    > -------------------------------------------------------
    >
    > Here instead of deleting both duplicate entry
    >
    > | mr x | 8th lane | 124364 |
    >
    > only one gets deleted (out of two) and no duplicate entries are there
    > in table.
    >
    > Hope it will clarify my question.
    >
    > Thanks.
    >
    > Manish
    >
    Manish,

    Is this a homework assignment? It sure looks like one.

    I would recommend a primary key for the table. It really helps prevent
    this from happening in the first place. And if it does happen it's
    easier to delete.

    For a single statement, though, the only way I can think of is to use a
    delete with LIMIT 1 and a subselect checking for count > 1. But it
    would be a bit complicated.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

Similar Threads

  1. Duplicate Database Entries
    By kabalah in forum Dreamweaver AppDev
    Replies: 1
    Last Post: April 7th, 01:28 PM
  2. Duplicate Entries
    By just1coder@yahoo.ca in forum ASP Database
    Replies: 4
    Last Post: July 28th, 02:50 PM
  3. DataView.Sort causing duplicate entries in a DataGrid control
    By Grant Harmeyer in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: June 15th, 07:05 PM
  4. Remove duplicate entries
    By Court in forum ASP Database
    Replies: 1
    Last Post: December 18th, 02:16 AM
  5. SYSLOG generating duplicate entries
    By Greg G in forum Linux / Unix Administration
    Replies: 3
    Last Post: November 1st, 01:05 PM

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