Hot to fix update using join not working in MySQL

Question

MySQL/InnoDB

我想将表country的language_id填充为:

id name      language language_id
1  USA       English  1
2  France    French   2
3  Indonesia NULL     NULL

来自:

language
id name
1  English
2  French

现在country.language_id没有值，我想从language表中填充

没有语言名称的国家(印度尼西亚)，其country.language_id可以为0或NULL

SQL:

UPDATE country
SET language_id = language.id,  
FROM country
INNER JOIN language ON country.language = language.name

不能用

设置完country.language_id后，我将删除country.language列，并在country.language_id和language.language_id上创建外键关系

Answer 1

我认为我们可以尝试使用OUTER JOIN的UPDATE语句，语句可能需要使用JOIN之前的SET在MySQL

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

所以我们可以在下面使用as。

UPDATE country
LEFT JOIN language ON country.language = language.name
SET country.language_id = language.id 

sqlfiddle

Answer 2

您可以简单地在更新查询中使用LEFT JOIN，如下所示:

UPDATE country c
LEFT JOIN language l ON c.language = l.name
SET language_id = l.id;

DB FIDDLE DEMO