使用另一个表中的数据更新表

Question

I have two tables one tbl_demographics with coluimns [staffid, natid] and another tbl_nationality with columns [natid, description].

HR sends me staff data in excel with the staffid and nationality which I upload as it is. Only problem is the nationality column contains data such as Canadian instead of (CAN) or Nigerian instead of (NGA).

How do i write a script in sql (or php even) that can update all the natid in tbl_demographics into data that is corresponding the correct natid in tbl_nationality? I'm using mysql server (and php if necessary).

The data they send:

staffid | natid

201001  | Canadian

202332  | Nigerian

Expected tables look:

staffid | natid (tbl_demographics)

201001  | CAN
202332  | NGA


natid   | description (tbl_nationality)

CAN     | Canada
NGA     | Nigeria

Answer 1

Use this, assuming you place the HR excel data in a table called tbl_excel (only SQL):

UPDATE tbl_demographics d, tbl_nationality n, tbl_excel e
SET d.nat_id = n.natid
WHERE n.description = e.description
  AND e.staffid = d.staffid

This of course needs the descriptions of the data they send (Canada, Canadian) match the descriptions on the tbl_nationality table.