Illegal mix of collations in mySQL

Question

I need to transfer a column from one table to another. The source table has a different collation than the target table (latin1_general_ci and latin1_swedish_ci).

I use

UPDATE target 
LEFT JOIN source ON target.artnr = source.artnr 
SET target.barcode = source.barcode

I get an "illegal mix of collations".

What is a quick fix to get this working without having to change either table? I tried CONVERT and COLLATE to run the whole operation in UTF-8, but that didn't help. "barcode" contains numeric data only (even though they all are VARCHARs), so there are no collation worries either way. I need to do this just once.

Edit: I sorted it using a CAST(fieldname as unsigned) on every field involved. I was able to do that in this case because only numeric data was affected, but it would be nice to know a more general approach to this, so I am leaving the question open.

Answer 1

How about joining on select with only 2 numerical columns?

UPDATE target
LEFT JOIN (
    SELECT 
        artnr COLLATE latin1_swedish_ci AS artnr,
        barcode COLLATE latin1_swedish_ci AS barcode
     FROM source
    ) source ON target.artnr = source.artnr
SET target.barcode = source.barcode

Answer 2

I tried CONVERT and COLLATE to run the whole operation in UTF-8, but that didn't help.

It bugged me, so today I created those tables and successfully ran this one:

UPDATE target
LEFT JOIN  source ON( target.artnr = source.artnr COLLATE latin1_swedish_ci )
SET target.barcode = source.barcode