MYSQL关联分类表按ingredient匹配更新status字段的语法咨询

现有两张表：food表（别名t1，包含ingredient、status字段）、categories分类表（别名t2，包含ingredient、category字段），需求为当t1和t2的ingredient字段匹配时，将t1的status更新为t2对应的category值，两种JOIN实现方式如下：

1. 使用INNER JOIN实现 #

INNER JOIN只会更新两张表ingredient完全匹配的记录，未匹配的t1记录不会做任何改动，完全符合需求场景。

MySQL语法 #

UPDATE food t1
INNER JOIN categories t2 
    ON t1.ingredient = t2.ingredient
SET t1.status = t2.category;

PostgreSQL/SQL Server语法 #

UPDATE t1
SET t1.status = t2.category
FROM food t1
INNER JOIN categories t2 
    ON t1.ingredient = t2.ingredient;

2. 使用LEFT JOIN实现 #

LEFT JOIN默认会匹配t1的所有记录，若要实现和INNER JOIN一致的仅更新匹配记录的效果，需要额外添加WHERE条件过滤掉无匹配的记录：

MySQL语法 #

UPDATE food t1
LEFT JOIN categories t2 
    ON t1.ingredient = t2.ingredient
SET t1.status = t2.category
-- 仅更新匹配到分类的记录，删除该行则无匹配的记录status会被设为NULL
WHERE t2.ingredient IS NOT NULL;

PostgreSQL/SQL Server语法 #

UPDATE t1
SET t1.status = t2.category
FROM food t1
LEFT JOIN categories t2 
    ON t1.ingredient = t2.ingredient
WHERE t2.ingredient IS NOT NULL;

如果去掉WHERE条件，t1中没有匹配到分类的记录的status会被覆盖为NULL，适合需要全量覆盖、无分类就标记为空的场景。

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