Nita Nita - 1 year ago 53
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


$del_duplicate_contacts = $mysqli->query("

FROM contacts ca
SELECT MAX(id) id, name1, tel, email
FROM contacts
GROUP BY name1, tel, email
) cb ON = AND
ca.name1 = cb.name1 AND = AND =



Example of table:

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

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?

Answer Source

Use order by in group_concat, you can try this:

DELETE c1 FROM contacts c1
        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 = AND = AND <>;

Demo Here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download