How to populate a column with a foreign key?

Question

MySQL

Good afternoon, please tell me: there is a table with a foreign key and, accordingly, a dictionary table for it
. There is also a text field in the main table where the same is stored as in the dictionary, but with text. The task is to fill in all fields with a foreign key in accordance with this text field and dictionary

Answer 1

This is done by an update with subqueries.

update table tb
      set tb.type_id = (select tp.id from types tp where tp.name = tb.type_name limit 1) -- выбираем подзапросом id из справочника по совпадению наименования поля в обновляемой таблице
 where tb.type_id is null -- страховка, что будем обновлять не установленные значения
     and exists(select 1 from types tp where tp.name = tb.type_name) -- будем ставить ключ, если есть наименование в справочнике types

PS: Before updating, make sure with select with the same where that you insert the values ​​you need into the fields of the set section. According to the principle of the saying: "Seven times select-no - once update-no".
PPS: It is advisable to avoid situations when you periodically need to normalize the data structure, ideally, when inserting, you need to immediately determine the key, and use the name field only when the desired name is not in the directory.

Answer 2

UPDATE ... JOIN