Nita Nita - 4 months ago 6
MySQL Question

MySQL - selective removing duplicated records

I have db with few 1000 of contacts and would like to delete all duplicated records. Sql query I have at the moment works well ( when in records - tel, email, name1 are duplicated). Query deletes duplicates with lower id then last occurring record. But in some cases another fields of the record a filled in already (important ones will by title and name2). What i would like to achieve is for mysql to check if these fields are filled in and keep only the record with most information filed in.

My Query

<?php

$del_duplicate_contacts = $mysqli->query("

DELETE ca
FROM contacts ca
LEFT JOIN
(
SELECT MAX(id) id, name1, tel, email
FROM contacts
GROUP BY name1, tel, email
) cb ON ca.id = cb.id AND
ca.name1 = cb.name1 AND
ca.tel = cb.tel AND
ca.email = cb.email
WHERE cb.id IS NULL

");

?>


Example of table:

ID title name1 name2 tel email
1 John 01234 1@1.com
2 Mr John Smith 01234 1@1.com
3 John 01234 1@1.com


My query will delete record 1 and 2. I would like to keep only nr 2 and delete 1 and 3.
How I can achieve that? Is is possible? Or maybe i should involve PHP, if so How?

JPG JPG
Answer

Use order by in group_concat, you can try this:

DELETE c1 FROM contacts c1
JOIN (
    SELECT 
        substring_index(group_concat(id ORDER BY ((title IS NULL OR title ='') AND (name2 IS NULL OR name2 = '')), id DESC), ',', 1) AS id,
        name1, tel, email
    FROM contacts
    GROUP BY name1, tel, email
) c2
ON c1.name1 = c2.name1 AND c1.tel = c2.tel AND c1.email = c2.email AND c1.id <> c2.id;

Demo Here

Comments