Handling Duplicates - MySQL - page 563

mysql> SELECT * FROM tmp ORDER BY last_name, first_name;
+-----------+-------------+--------------------------+
| last_name | first_name | street |
+-----------+-------------+--------------------------+
| Baxter | Wallace | 57 3rd Ave. |
| Brown | Bartholomew | 432 River Run |
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| McTavish | Taylor | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
+-----------+-------------+--------------------------+
mysql> DROP TABLE catalog_list;
mysql> RENAME TABLE tmp TO catalog_list;
The unique index prevents rows with duplicate key values from being inserted into
tmp , and IGNORE tells MySQL not to stop with an error if a duplicate is found. One
shortcoming of this method is that if the indexed columns can contain NULL values, you
must use a UNIQUE index rather than a PRIMARY KEY , in which case the index will not
remove duplicate NULL keys. ( UNIQUE indexes permit multiple NULL values.) This method
does prevent occurrence of duplicates in the future.
Removing duplicates of a particular row
You can use LIMIT to restrict the effect of a DELETE statement to a subset of the rows that
it otherwise would delete. This makes the statement applicable to removing duplicate
rows. Suppose that the original unindexed catalog_list table contains duplicates:
mysql> SELECT COUNT(*), last_name, first_name
-> FROM catalog_list
-> GROUP BY last_name, first_name
-> HAVING COUNT(*) > 1;
+----------+-----------+------------+
| COUNT(*) | last_name | first_name |
+----------+-----------+------------+
| 3 | Baxter | Wallace |
| 2 | Pinter | Marlene |
+----------+-----------+------------+
To remove the extra instances of each name, do this:
mysql> DELETE FROM catalog_list WHERE last_name = 'Baxter'
-> AND first_name = 'Wallace' LIMIT 2;
mysql> DELETE FROM catalog_list WHERE last_name = 'Pinter'
-> AND first_name = 'Marlene' LIMIT 1;
mysql> SELECT * FROM catalog_list;
+-----------+-------------+--------------------------+
| last_name | first_name | street |
+-----------+-------------+--------------------------+
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| McTavish | Taylor | 432 River Run |
| Brown | Bartholomew | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |