Deleting duplicate entries from MySQL database table

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Manish
    #1

    Deleting duplicate entries from MySQL database table

    The topic is related to MySQL database.

    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 |
    -------------------------------------------------------
    >Please suggest on this
    Tags: None
    • Alvaro G. Vicario
      #2
      Re: Deleting duplicate entries from MySQL database table

      *** Manish escribió/wrote (7 Jul 2006 04:11:54 -0700):
      >>Please suggest on this
      If you don't make a question I don't really what you kind of suggestions
      you are expecting. Anyway, the best way to avoid duplicate information is
      having a good design so the database is normalised and using primary keys
      and unique indexes so the dupes cannot be physically inserted.


      --
      -+ http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
      ++ Mi sitio sobre programación web: http://bits.demogracia.com
      +- Mi web de humor con rayos UVA: http://www.demogracia.com
      --

        Comment

        • Richard Levasseur
          #3
          Re: Deleting duplicate entries from MySQL database table

          I think he wants to get a distinct set of all the addresses.

          Use the group by clause or the distinct modifier.

          Alvaro G. Vicario wrote:
          *** Manish escribió/wrote (7 Jul 2006 04:11:54 -0700):
          >Please suggest on this
          >
          If you don't make a question I don't really what you kind of suggestions
          you are expecting. Anyway, the best way to avoid duplicate information is
          having a good design so the database is normalised and using primary keys
          and unique indexes so the dupes cannot be physically inserted.
          >
          >
          --
          -+ http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
          ++ Mi sitio sobre programación web: http://bits.demogracia.com
          +- Mi web de humor con rayos UVA: http://www.demogracia.com
          --

            Comment

            • Manish
              #4
              Re: Deleting duplicate entries from MySQL database table

              Sorry for not formulating the question properly.

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

              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


              Alvaro G. Vicario wrote:
              *** Manish escribió/wrote (7 Jul 2006 04:11:54 -0700):
              >Please suggest on this
              >
              If you don't make a question I don't really what you kind of suggestions
              you are expecting. Anyway, the best way to avoid duplicate information is
              having a good design so the database is normalised and using primary keys
              and unique indexes so the dupes cannot be physically inserted.
              >
              >
              --
              -+ http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
              ++ Mi sitio sobre programación web: http://bits.demogracia.com
              +- Mi web de humor con rayos UVA: http://www.demogracia.com
              --

                Comment

                • Jerry Stuckle
                  #5
                  Re: Deleting duplicate entries from MySQL database table

                  Manish wrote:
                  The topic is related to MySQL database.
                  >
                  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 |
                  -------------------------------------------------------
                  >
                  >
                  >>>Please suggest on this
                  >
                  >
                  Manish,

                  Since your question is related to a MySQL database, might I suggest you
                  ask in a MySQL newsgroup - like comp.databases. mysql?

                  That's where the MySQL gurus hang out.

                  --
                  =============== ===
                  Remove the "x" from my email address
                  Jerry Stuckle
                  JDS Computer Training Corp.
                  jstucklex@attgl obal.net
                  =============== ===

                    Comment

                    • Richard Levasseur
                      #6
                      Re: Deleting duplicate entries from MySQL database table

                      Manish wrote:
                      Sorry for not formulating the question properly.
                      >
                      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 login bug.
                      >
                      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
                      >
                      >
                      Use distinct and create a new table.

                      INSERT INTO nodupes VALUES SELECT DISTINCT * FROM dupes

                      the distinct modifier will make sure all the rows of the result set are
                      different.

                      Note: this is under the assumption that all fields of the duplicate
                      rows are the same.

                      Another way is to create second table and set the primary key to
                      whatever should be unique part. Then do INSERT INTO IGNORE nodupes
                      VALUES SELECT * FROM dupes
                      The ignore portion will ignore any duplicate key errors.
                      Alvaro G. Vicario wrote:
                      *** Manish escribió/wrote (7 Jul 2006 04:11:54 -0700):
                      >>Please suggest on this
                      If you don't make a question I don't really what you kind of suggestions
                      you are expecting. Anyway, the best way to avoid duplicate information is
                      having a good design so the database is normalised and using primary keys
                      and unique indexes so the dupes cannot be physically inserted.


                      --
                      -+ http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
                      ++ Mi sitio sobre programación web: http://bits.demogracia.com
                      +- Mi web de humor con rayos UVA: http://www.demogracia.com
                      --

                        Comment

                        Previous template Next
                        Working...