MySQL Workbench中使用UPDATE语句的FROM子句时语法错误求助

你的代码问题在于MySQL不支持UPDATE ... FROM ...这种写法，这是PostgreSQL等数据库的语法规范，MySQL需要用UPDATE ... JOIN ...的方式关联子查询。另外还要处理上季度数据为空（每个郡/州的第一条记录）或者为0的情况，避免计算报错。

修正后的代码如下：

UPDATE housing AS current
JOIN (
    SELECT
        county,
        state,
        year,
        quarter,
        medListingPrice,
        LAG(medListingPrice) OVER (PARTITION BY county, state ORDER BY year, quarter) AS prevMLP
    FROM housing
) AS previous
ON current.county = previous.county
   AND current.state = previous.state
   AND current.year = previous.year
   AND current.quarter = previous.quarter
SET current.price_change = 
    CASE 
        WHEN previous.prevMLP IS NOT NULL AND previous.prevMLP != 0 
        THEN ((current.medListingPrice - previous.prevMLP) / previous.prevMLP) * 100
        ELSE NULL -- 若希望无数据时显示0，可替换为0
    END;

关键说明：

• 用JOIN替代FROM来关联子查询，符合MySQL的UPDATE语法规范
• 新增CASE判断：当prevMLP为空（第一条记录没有上季度数据）或为0时，返回NULL（或自定义值），避免出现除以0的错误
• 保持LAG函数的分区和排序逻辑，确保每个郡/州按年份、季度顺序取上一季度的中位挂牌价

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