-- update patient ref source
UPDATE patient p
JOIN migrations.SMIG_8039_import_contact ic ON p.patient_id = ic.patient_id
LEFT JOIN ref_source rs ON rs.ref_source_id = p.ref_source_id
LEFT JOIN migrations.SMIG_8039_ref_source tmp_rs ON tmp_rs.external_ref_source = rs.ref_source_name
LEFT JOIN ref_source rs2 ON rs2.ref_source_name = tmp_rs.external_ref_source
SET p.ref_source_id = rs2.ref_source_id
WHERE rs.ref_source_name IS NOT NULL;