Update with sub query troubling me

Databases
1

trying to update records in one table, with values from another because I have added a new column.

I need to take the two char country code from one table and put it into another with an update statement. The rows in which I input this new value, must contain the corresponding three char country code.

the three char country code is in both tables so shouldn’t I be joining them, to ensure the two char code goes into the correct rows?

this query below fails because the sub query brings in more than one row.


update country_codes
set two_char_country_code = (
select distinct
ip.two_char_country_code
, ip.three_char_country_code
from ip_to_country AS ip
where country_codes.three_char_country_code = ip.three_char_country_code

sorry for being so quick to have gone rusty. :frowning:

bazz

2

is this what you wanted?

UPDATE country_codes
INNER
  JOIN ip_to_country 
    ON ip_to_country.three_char_country_code = country_codes.three_char_country_code 
   SET country_codes.two_char_country_code = ip_to_country.two_char_country_code