MySQL关联公司表更新联系人表companyID字段的语法问题

我从CSV导入两张表：a_test_company（8000条记录）和a_test_contact（11000条记录），联系人表的companyName与公司表的companyName匹配，需要为联系人表填充companyID字段。不想用PHP循环查询更新，想在PHPMyAdmin中用SQL实现，但改写参考示例后执行报错：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM a_test_company
JOIN a_test_company
ON a_test_contact.companyName = a_test' at line 3

参考的示例SQL（属于SQL Server语法）：

UPDATE ci
SET ci.CountryId = co.CountryId
FROM tblCity ci
JOIN tblCountry co
ON ci.ISO2 = co.CountryISO2
AND ci.ISO3 = co.CountryISO3
WHERE ci.CountryId IS NULL

我的两次尝试代码：
第一次：

UPDATE ci
SET ci.companyID = co.companyID
FROM a_test_contact ci
JOIN a_test_company co
ON ci.companyName = co.companyName
WHERE ci.companyID IS NULL

第二次：

UPDATE a_test_contact
SET a_test_contact.companyID = a_test_company.companyID
FROM a_test_contact
JOIN a_test_company
ON a_test_contact.companyName = a_test_company.companyName
WHERE a_test_contact.companyID IS NULL

表结构及预期结果 #

a_test_company表：

+---------------+---------------+
| companyID     | companyName   | 
+---------------+---------------+
| 1             | companyA      | 
| 2             | companyB      | 
| 3             | companyC      | 
| 4             | companyD      |  
+---------------+---------------+

a_test_contact表（更新前）：

+---------------+---------------+---------------+---------------+
| contactID     | contactName   | companyName   | companyID     |
+---------------+---------------+---------------+---------------+
| 1             | contactA      | companyA      |  NULL         |
| 2             | contactB      | companyA      |  NULL         |
| 3             | contactC      | companyB      |  NULL         |
| 4             | contactD      | companyC      |  NULL         |
| 5             | contactE      | companyC      |  NULL         |
+---------------+---------------+---------------+---------------+

预期更新结果：

+---------------+---------------+---------------+---------------+
| contactID     | contactName   | companyName   | companyID     |
+---------------+---------------+---------------+---------------+
| 1             | contactA      | companyA      | 1             |
| 2             | contactB      | companyA      | 1             |
| 3             | contactC      | companyB      | 2             |
| 4             | contactD      | companyC      | 3             |
| 5             | contactE      | companyC      | 3             |
+---------------+---------------+---------------+---------------+

正确的MySQL更新语句 #

之前的错误是因为参考的是SQL Server的UPDATE...FROM语法，MySQL不支持这种写法，需要使用MySQL专属的关联更新语法：

带别名的简洁写法

UPDATE a_test_contact ci
JOIN a_test_company co
ON ci.companyName = co.companyName
SET ci.companyID = co.companyID
WHERE ci.companyID IS NULL;

无别名的直观写法

UPDATE a_test_contact
JOIN a_test_company
ON a_test_contact.companyName = a_test_company.companyName
SET a_test_contact.companyID = a_test_company.companyID
WHERE a_test_contact.companyID IS NULL;

说明 #

MySQL的关联更新逻辑是将JOIN子句直接放在UPDATE之后，而非通过FROM引入关联表。该语句可以高效批量更新所有符合条件的联系人记录，避免PHP循环带来的性能损耗。

内容的提问来源于stack exchange，提问作者Rayza