MySQL - selective deletion of duplicate records

Question

I have a db with several thousand contacts and you want to delete all duplicate entries. Sql-request, which currently works fine for me (when in files - tel , email , name1 are duplicated). The request deletes duplicates with a lower identifier and then the last meeting. But in some cases, other fields of the record are already filled (the important ones will be title and name2 ). What I would like to achieve is to check whether these fields are really filled in and to keep only the record with the most information recorded in. Mysql.

My request

<?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 table:

ID   title   name1   name2    tel      email
1            John             01234    [e-mail]
2    Mr      John     Smith   01234    [e-mail]
3            John             01234    [e-mail]

1 2. nr 2 1 3. ? ? , , PHP, ?

Answer 1

order by group_concat, :

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

Answer 2

OOPS - , - , , , - , , , :

DELETE FROM contacts WHERE ID IN (
  SELECT ID FROM (
    SELECT DISTINCT a.ID
    FROM contacts AS a
    JOIN contacts AS b
    ON  a.name1 = b.name1
    AND a.tel = b.tel
    AND a.email = b.email
    ORDER BY a.name1 DESC, a.name2 DESC, a.title DESC
    LIMIT 1,100000
  ) AS tmp
)

LIMIT 1, xxxx - 0, xxxx,

, , , ,

, :

SELECT * FROM contacts WHERE ID IN (
  SELECT ID FROM (
    SELECT DISTINCT a.ID
    ...
    LIMIT 1,100000
  ) AS tmp
)

, , db

=====================================

:

, :

, , # 2 # 4 # 5. :

, , , , :

:

SQL, , db:

DELETE FROM contacts WHERE ID NOT IN (
  SELECT * FROM (
    SELECT ID FROM (
      SELECT * FROM contacts ORDER BY title DESC, name1 DESC, name2 DESC, tel DESC, email DESC
    ) AS tmp
    GROUP BY name1, tel, email
  ) AS del
)

Answer 3

, NOT EXIST NOT IN

DELETE FROM contacts 
WHERE NOT EXISTS (
    SELECT 1 FROM (
       SELECT * FROM ( 
          SELECT * FROM contact AS tmp ORDER BY title DESC, name1 DESC, name2 DESC, email DESC, tel DESC ) 
       as tbl group by name1) 
    as test WHERE contact.id= test.id 
)

Answer 4

- !

DELETE FROM contacts where ID NOT IN (
    SELECT ID FROM ( Select A.ID from contacts as A 
                    join contacts AS B 
                    ON A.name1 = B.name1 
                    AND A.name2 = B.name2
                    AND A.tel = B.tel 
                    AND A.email = B.email) As mytry);