Deleting duplicate entries from MySQL database table

[Manish]

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

[Alvaro G. Vicario]

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

[Richard Levasseur]

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

[Manish]

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

[Jerry Stuckle]

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.
js*******@attglobal.net
==================

[Richard Levasseur]

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