Removing Duplicate

Question

I have a lot of telephone numbers that are duplicated in the telephone field. How can it be done by SQL?

I want to remove record that are duplicate.

I also want to know how many that are duplicated?

Thanks

possible duplicate of http://stackoverflow.com/q/8567007/27535 or http://stackoverflow.com/q/8590010/27535 and many more — gbn, Jan 31 '12 at 16:14
Must you use only SQL? Cant you write a bit code for that? It will be much easier with a code fuction — Bhrugesh Patel, Jan 31 '12 at 16:16
possible duplicate of [remove duplicates in mysql database](http://stackoverflow.com/questions/8793231/remove-duplicates-in-mysql-database) — aF., Jan 31 '12 at 16:28

score 1 · Answer 1 · answered Jan 31 '12 at 16:15

Try this:

DELETE FROM phonenumbers WHERE telephone = "[phone number here]" AND id NOT IN (SELECT id FROM phonenumbers WHERE telephone = "[phone number here]" LIMIT 1)

This will remove all entries with that phone number, except the first one

Note, this is assuming you have a unique identifier ID in your table. (and your tablename is phonenumbers. Change that into your real tablename

score 0 · Answer 2 · answered Jan 31 '12 at 17:27

This query might help:

DELETE `P`.*
FROM `phones` `P` 
LEFT JOIN ( 
    SELECT `telephone`, MIN(`id`) `ids` 
    FROM `phones` 
    GROUP BY `telephone` 
) `TA` ON `P`.`id` = `TA`.`ids` 
WHERE `TA`.`ids` IS NULL;

Please note to change the table names and field names as per your schema. Also, the above assumes that your table has a primary column, denoted as id in the above query.

The logic is:

using the subquery, we first find out all telephone numbers and the first record for each number. These are the records that will remain and the rest deleted
then we do a left join between "phones" table and the derived table, and delete all records from "phones" that do not match in the derived table

The benefit with the above query is that it will delete all duplicate records in one shot.

For the duplicate counts, you may do something like:

SELECT `telephone`, COUNT(1) `cnt` 
FROM `phones` 
GROUP BY `telephone` 
HAVING COUNT(1) > 1

Hope it helps!

score 0 · Answer 3 · answered Jan 31 '12 at 19:14

Here's a simple one that copies your table to a new one lacking duplicate 'telephone' fields:

CREATE TABLE addrbook2
  SELECT * FROM addrbook GROUP BY telephone

You could then delete the old addrbook table, and rename the new addrbook2 to addrbook if you wanted.

Removing Duplicate

3 Answers3