Some issues related with Unified State Examination in Informatics in Russian Federation: How To Delete Duplicate Rows in MySQL (MariaDB)

Тестировано на Fedora 35 Server

Case 1

1. Пример удаления дублей на 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)

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

Duplicates 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)

*********

Case 2

*********

MariaDB [testcte]> use testdups ;

Database changed

MariaDB [testdups]> DROP TABLE IF EXISTS contacts;

Query OK, 0 rows affected, 1 warning (0.000 sec)

MariaDB [testdups]>

MariaDB [testdups]> 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.006 sec)

MariaDB [testdups]> 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 [testdups]> SELECT id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY email ) AS row_num

-> FROM contacts;

+----+---------------------------------+---------+

| id | email | row_num |

+----+---------------------------------+---------+

| 1 | carine.schmitt@verizon.net | 1 |

| 4 | janine.labrune@aol.com | 1 |

| 6 | janine.labrune@aol.com | 2 |

| 2 | jean.king@me.com | 1 |

| 12 | jean.king@me.com | 2 |

| 5 | jonas.bergulfsen@mac.com | 1 |

| 10 | julie.murphy@yahoo.com | 1 |

| 11 | kwai.lee@google.com | 1 |

| 3 | peter.ferguson@google.com | 1 |

| 9 | roland.keitel@yahoo.com | 1 |

| 14 | roland.keitel@yahoo.com | 2 |

| 7 | susan.nelson@comcast.net | 1 |

| 13 | susan.nelson@comcast.net | 2 |

| 8 | zbyszek.piestrzeniewicz@att.net | 1 |

+----+---------------------------------+---------+

14 rows in set (0.001 sec)

MariaDB [testdups]> SELECT id FROM (SELECT id, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY email) AS row_num FROM contacts ) t WHERE row_num > 1;

+----+

| id |

+----+

| 6 |

| 12 |

| 14 |

| 13 |

+----+

4 rows in set (0.001 sec)

MariaDB [testdups]> DELETE FROM contacts

-> WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY email) AS row_num FROM contacts) t

-> WHERE row_num > 1 );

Query OK, 4 rows affected (0.002 sec)

MariaDB [testdups]> select * from contacts ;

+----+------------+-----------------+---------------------------------+

| id | first_name | last_name | email |

+----+------------+-----------------+---------------------------------+

| 1 | Carine | Schmitt | carine.schmitt@verizon.net |

| 2 | Jean | King | jean.king@me.com |

| 3 | Peter | Ferguson | peter.ferguson@google.com |

| 4 | Janine | Labrune | janine.labrune@aol.com |

| 5 | Jonas | Bergulfsen | jonas.bergulfsen@mac.com |

| 7 | Susan | Nelson | susan.nelson@comcast.net |

| 8 | Zbyszek | Piestrzeniewicz | zbyszek.piestrzeniewicz@att.net |

| 9 | Roland | Keitel | roland.keitel@yahoo.com |

| 10 | Julie | Murphy | julie.murphy@yahoo.com |

| 11 | Kwai | Lee | kwai.lee@google.com |

+----+------------+-----------------+---------------------------------+

10 rows in set (0.001 sec)

References

https://www.mysqltutorial.org/mysql-delete-duplicate-rows/

Some issues related with Unified State Examination in Informatics in Russian Federation

Tuesday, April 19, 2022

How To Delete Duplicate Rows in MySQL (MariaDB)

No comments:

Post a Comment

Report Abuse