MySQL / InnoDB
I want to fill language_id of table country as:
id name language language_id
1 USA English 1
2 France French 2
3 Indonesia NULL NULL
From:
language
id name
1 English
2 French
Right now country.language_id has no values, which I want to fill from language table
The country which has no language name (Indonesia), its country.language_id can be 0 or NULL
SQL:
UPDATE country
SET language_id = language.id,
FROM country
INNER JOIN language ON country.language = language.name
Its not working.
After setting country.language_id, I will delete country.language column and create foreign key relationship on country.language_id and language.language_id
CodePudding user response：
I think we can try to use OUTER JOIN for that UPDATE statement, and the statement might need to use JOIN before SET in MySQL
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
so we might use as below.
UPDATE country
LEFT JOIN language ON country.language = language.name
SET country.language_id = language.id
CodePudding user response：
You can simply use a LEFT JOIN in your update query like so:
UPDATE country c
LEFT JOIN language l ON c.language = l.name
SET language_id = l.id;