Fix duplicate artists

The ARTigo database was build ignoring duplicate artists, which is very bad for statistic results.
To get an overview about the state, you can create a new column containing the full name of the artists first.

ALTER TABLE artists 
ADD fullname VARCHAR(250) 
AFTER surname;

Now update the column as a concatenation of both forename and surname. As CONCAT returns an empty string if any field is empty, we replace empty values by using IFNULL.

UPDATE artists 
SET `fullname` = CONCAT(IFNULL(`firstname`,""), " ", IFNULL(`surname`,""))

Let us check how many artists have the exact same name.

SELECT forename, surname, COUNT(*) c 
FROM artist 
GROUP BY fullname 
HAVING c > 1 
ORDER BY c desc;

We get 393 results that need to be fixed. We now want to ignore slightly different writings. So we use MySQLs SOUNDEX function to generate the SOUNDEX representations of the full names and store them in a different column.

ALTER TABLE artists 
ADD soundex VARCHAR(35) 
AFTER fullname;

UPDATE artists 
SET `soundex` = SOUNDEX(fullname);

To get an overview we use

SELECT forename, surname, COUNT(*) AS count 
FROM artist 
GROUP BY soundex 
HAVING count > 1 
ORDER BY count desc;

Which results in 1973 entries with duplicate artists with the same name, but different amounts of individual duplicate occurrences. Altogether around 25615 wrong, unnecessary entries. To clarify that further, there is only one entry needed without any name, not 17360 ones.

+--------------------------------+------------------------------------------------+-------+
| forename                       | surname                                        | c     |
+--------------------------------+------------------------------------------------+-------+
|                                |                                                | 17360 |
| NULL                           | Rembrandt Harmensz. van Rijn                   |  1379 |
| NULL                           | Meissener Porzellan Manufaktur                 |  1004 |
| NULL                           | Manufactuur Oud-Loosdrecht                     |   241 |
|                                | Unknown                                        |   236 |
| NULL                           | Woodbury & Page                                |   200 |
| NULL                           | Monogrammist CK (1590)                         |   142 |
| NULL                           | Desguerrois & Co.                              |   108 |
| NULL                           | Meester van Delft                              |    99 |
.......

SELECT *, COUNT(*) c FROM artist GROUP BY soundex HAVING c > 1;