How to update preferred language depending on a custom field in imported contacts?

Question

I have imported hundreds of contacts having a custom field called "Langue" in set "ocsr" and having two possible values, 'F' and 'N'. I want to transfer this information into the field preferred language, with values 'fr_FR' and 'nl_NL'.

I suppose it is possible to do it in one Mysql operation, but I don't find where the custom field value is stored:

update civicrm_contact set preferred_language = 'fr_FR' where ... ocsr::Langue ... = 'F'

Any hint would be appreciated.

Answer 1

It will be in a table called something like civicrm_value_ocsr_12 and the fieldname will look something like langue_57. You want to join on the entity_id column, e.g.

update civicrm_contact c left join civicrm_value_ocsr_12 v ON v.entity_id = c.id set c.preferred_language = 'fr_FR' where v.langue_57='F';

(I haven't tested that, just writing out the theory - I always get mixed up trying to get the right syntax for joins in an update statement.)