MySQL中UPDATE结合SET与FROM的语法错误及正确用法

一、原语句的问题 #

1. 数据库语法不兼容：UPDATE ... FROM这种写法是PostgreSQL、SQL Server的专属语法，MySQL完全不支持，在MySQL环境下执行会直接报错。
2. 字段歧义：就算是在支持该语法的数据库（比如PostgreSQL）中，WHERE q_id=343里的q_id未指定所属表，数据库无法判断你要匹配的是主表answer的q_id还是子查询newdata里的q_id，会导致逻辑错误或报错。

二、正确写法 #

针对MySQL环境 #

MySQL需要用JOIN关联的方式实现多表更新：

UPDATE answer a
JOIN (
    SELECT expiry, created_at, seller_id
    FROM answer
    WHERE q_id=49
      AND status='APPROVED'
      AND expiry IS NOT NULL
      AND expiry < '2028-01-01 00:00:00'
) newdata 
ON a.created_at = newdata.created_at 
AND a.seller_id = newdata.seller_id
SET a.expiry = newdata.expiry
WHERE a.q_id=343;

针对PostgreSQL/SQL Server环境 #

只需给主表的字段加上表名前缀，消除字段歧义即可：

UPDATE answer
SET expiry = newdata.expiry
FROM (
    SELECT expiry, created_at, seller_id
    FROM answer
    WHERE q_id=49
      AND status='APPROVED'
      AND expiry IS NOT NULL
      AND expiry < '2028-01-01 00:00:00'
) newdata
WHERE answer.q_id=343
  AND answer.created_at = newdata.created_at
  AND answer.seller_id = newdata.seller_id;

注意事项 #

• 无论使用哪种数据库，都要明确指定字段所属的表，避免字段名歧义；
• 确认created_at+seller_id的组合能精准匹配目标记录，防止误更新无关数据。

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