CODEWITHSUNDEEP
mysqlsqldatabase
I'll-Be-Back
I'll-Be-Back

Reputation: 10828

Removing Duplicate

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

Upvotes: 1

Views: 185

Answers (3)

gcbenison
gcbenison

Reputation: 11963

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.

Upvotes: 0

Abhay
Abhay

Reputation: 6645

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:

  1. 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
  2. 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!

Upvotes: 0

Rene Pot
Rene Pot

Reputation: 24815

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

Upvotes: 1

Related Questions