Пример удаления дублей на Mariadb Server version: 10.5.13-MariaDB MariaDB Server
=====================
MariaDB [testcte]> CREATE TABLE contacts (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> first_name VARCHAR(50) NOT NULL,
-> last_name VARCHAR(50) NOT NULL,
-> email VARCHAR(255) NOT NULL
-> );
Query OK, 0 rows affected (0.010 sec)
MariaDB [testcte]> INSERT INTO contacts (first_name,last_name,email)
-> VALUES ('Carine ','Schmitt','carine.schmitt@verizon.net'),
-> ('Jean','King','jean.king@me.com'),
-> ('Peter','Ferguson','peter.ferguson@google.com'),
-> ('Janine ','Labrune','janine.labrune@aol.com'),
-> ('Jonas ','Bergulfsen','jonas.bergulfsen@mac.com'),
-> ('Janine ','Labrune','janine.labrune@aol.com'),
-> ('Susan','Nelson','susan.nelson@comcast.net'),
-> ('Zbyszek ','Piestrzeniewicz','zbyszek.piestrzeniewicz@att.net'),
-> ('Roland','Keitel','roland.keitel@yahoo.com'),
-> ('Julie','Murphy','julie.murphy@yahoo.com'),
-> ('Kwai','Lee','kwai.lee@google.com'),
-> ('Jean','King','jean.king@me.com'),
-> ('Susan','Nelson','susan.nelson@comcast.net'),
-> ('Roland','Keitel','roland.keitel@yahoo.com');
Query OK, 14 rows affected (0.002 sec)
Records: 14 Duplicates: 0 Warnings: 0
MariaDB [testcte]> SELECT * FROM contacts
-> ORDER BY email;
+----+------------+-----------------+---------------------------------+
| id | first_name | last_name | email |
+----+------------+-----------------+---------------------------------+
| 1 | Carine | Schmitt | carine.schmitt@verizon.net |
| 4 | Janine | Labrune | janine.labrune@aol.com |
| 6 | Janine | Labrune | janine.labrune@aol.com |
| 2 | Jean | King | jean.king@me.com |
| 12 | Jean | King | jean.king@me.com |
| 5 | Jonas | Bergulfsen | jonas.bergulfsen@mac.com |
| 10 | Julie | Murphy | julie.murphy@yahoo.com |
| 11 | Kwai | Lee | kwai.lee@google.com |
| 3 | Peter | Ferguson | peter.ferguson@google.com |
| 9 | Roland | Keitel | roland.keitel@yahoo.com |
| 14 | Roland | Keitel | roland.keitel@yahoo.com |
| 7 | Susan | Nelson | susan.nelson@comcast.net |
| 13 | Susan | Nelson | susan.nelson@comcast.net |
| 8 | Zbyszek | Piestrzeniewicz | zbyszek.piestrzeniewicz@att.net |
+----+------------+-----------------+---------------------------------+
14 rows in set (0.001 sec)
MariaDB [testcte]> SELECT
-> email, COUNT(email)
-> FROM
-> contacts
-> GROUP BY
-> email
-> HAVING
-> COUNT(email) > 1;
+--------------------------+--------------+
| email | COUNT(email) |
+--------------------------+--------------+
| janine.labrune@aol.com | 2 |
| jean.king@me.com | 2 |
| roland.keitel@yahoo.com | 2 |
| susan.nelson@comcast.net | 2 |
+--------------------------+--------------+
4 rows in set (0.001 sec)
======================
Duplcaets delete SQL
======================
MariaDB [testcte]> DELETE t1 FROM contacts t1
-> INNER JOIN contacts t2
-> WHERE
-> t1.id < t2.id AND
-> t1.email = t2.email;
Query OK, 4 rows affected (0.003 sec)
MariaDB [testcte]> commit;
Query OK, 0 rows affected (0.000 sec)
=======================
MariaDB [testcte]> SELECT
-> email,
-> COUNT(email)
-> FROM
-> contacts
-> GROUP BY
-> email
-> HAVING
-> COUNT(email) > 1;
Empty set (0.001 sec)
MariaDB [testcte]> SELECT * FROM contacts;
+----+------------+-----------------+---------------------------------+
| id | first_name | last_name | email |
+----+------------+-----------------+---------------------------------+
| 1 | Carine | Schmitt | carine.schmitt@verizon.net |
| 3 | Peter | Ferguson | peter.ferguson@google.com |
| 5 | Jonas | Bergulfsen | jonas.bergulfsen@mac.com |
| 6 | Janine | Labrune | janine.labrune@aol.com |
| 8 | Zbyszek | Piestrzeniewicz | zbyszek.piestrzeniewicz@att.net |
| 10 | Julie | Murphy | julie.murphy@yahoo.com |
| 11 | Kwai | Lee | kwai.lee@google.com |
| 12 | Jean | King | jean.king@me.com |
| 13 | Susan | Nelson | susan.nelson@comcast.net |
| 14 | Roland | Keitel | roland.keitel@yahoo.com |
+----+------------+-----------------+---------------------------------+
10 rows in set (0.000 sec)
MariaDB [testcte]>