Mysql : How to update a table field to a value fetched from another table

Question

I have two table let's name A and B having following field :

TABLE A

| ID | COUNTRY_CODE | COUNTRY_NAME | FIRST_NAME | LAST_NAME |

TABLE B

| ID | COUNTRY_CODE | COUNTRY_NAME |

Now I need to update country_code field from Table A whose value is to be fetched from Table B.

The pseudo-code is something like this :

for all rows in Table A :
  set A.country_code = (select B.country_code from B where B.country_name = A.country_name

score 1 · Accepted Answer · answered May 21 '19 at 06:19

1

Use update with JOIN

update TableA A
inner join tableB B on B.country_name = A.country_name
set A.country_code=B.Country_code

answered May 21 '19 at 06:19

Fahmi

Shawn.X · Answer 2 · 2019-05-21T06:29:46.880

0

No need join, just try sql as below:

update TableA set country_code = B.country_code from TableB B where A.country_name = B.country_name

edited May 21 '19 at 06:29

answered May 21 '19 at 06:26

Shawn.X

not a valid mysql syntax – Fahmi May 21 '19 at 06:29
1

Yep..I just tried this in Mysql, it failed. But it will work in a PostgreSQL update. @fa06 – Shawn.X May 21 '19 at 06:32

2 Answers2