I am trying to perform an UPDATE involving two different databases using MySQL. The query I have is the following:
UPDATE sm4rtuniversity.members SET chamilo_id = (SELECT cu.id FROM chamilo.user cu
INNER JOIN sm4rtuniversity.members sm ON cu.email=sm.email);
And when executing it, it throws the following error: Illegal mix of collations (utf8_unicode_ci, IMPLICIT) and (utf8_spanish_ci, IMPLICIT) for operation '='
The error informs you that the fields you are trying to match are defined in different COLLATIONs, which are character sets (if I remember correctly).
I suggest you add or force the COLLATION of the fields you are matching, so that they speak the same language, as you might say. In your case, I will try to give you an idea modifying your query, assuming that the table of the UPDATE is the utf8_unicode_ci and that of the SELECT the utf8_spanish_ci , but it is you who will have to decide where to place the changes, according to your specifications:
UPDATE sm4rtuniversity.members SET chamilo_id = (SELECT cu.id COLLATE utf8_unicode_ci
FROM chamilo.user cu
INNER JOIN sm4rtuniversity.members sm ON cu.email=sm.email);
Research this link more about it
By the way, you are missing a LIMIT 1 at the end of SELECT to be 100% sure you will not get more than one record from the other table.