如何根据ID匹配更新表列？

Question

applicants.sql

full name | company Id    |      CompanyLabel
Johny Doe |     1         |    
Johny ben |     2         |  

companylist.sql

companyid  | Company name     | CompanyLabel
1   | Starks Industry  | Starks Industry - New York
2   | Captain Industry | Captain Industry - Paris

问题是，我有5000名申请者，他们没有1000家不同公司的CompanyLabel。

我正在使用它来获取CompanyLabel 1 by 1

UPDATE applicants set CompanyLabel = 'Starks Industry - New York' WHERE companyid = 1

如何在1个查询中执行所有这些操作？

Answer 1

您可以使用join和update来完成此操作，如下所示

update applicants a
join companylist c on c.companyid = a.companyid
set a.CompanyLabel = c.CompanyLabel