Home‎ > ‎MySQL‎ > ‎

Copy column from table to table in MySQl based on some id

Lets assume table1 contains list of country names with columns translated to English and Spanish and table2 contains only some selected countries with only English column, I want to add Spanish column. Both tables contain id country code.

Table: countries1

 code        country_english country_spanish
 AL Albania Albania
 DZ Algeria Argelia
 AD Andorra Andorra
 AO Angola Angola     

Table: countries2

 code        country_english country_spanish
 AL Albania
 AO Angola     

Result table:

 code        country_english country_spanish
 AL Albania Albania
 AO Angola Angola     


UPDATE countries2 SET `country_spanish` = (select `country_spanish` from countries1 where code = countries1.code)