Deleting duplicate entries from database table

[Manish]

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

[Jerry Stuckle]

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]jstucklex@attglobal.net[/email]
==================