使用最近100条Quotes类型记录均价更新产品estimated_price字段SQL报错

错误原因 #

Unknown column 'vtiger_products.productid' in 'where clause'是因为MySQL的子查询作用域限制：多层嵌套的子查询中，最内层子查询无法直接引用最外层vtiger_products表的字段，跨层级的表引用会被判定为不存在。

修正后的SQL脚本 #

使用窗口函数先筛选每个产品的最新100条Quotes记录，再计算均价后关联更新，避免作用域问题：

UPDATE vtiger_products p
JOIN (
    SELECT 
        productid,
        AVG(unit_price) AS avg_price
    FROM (
        SELECT 
            ip.productid,
            ip.listprice AS unit_price,
            ROW_NUMBER() OVER (
                PARTITION BY ip.productid 
                ORDER BY c.createdtime DESC
            ) AS rn
        FROM vtiger_inventoryproductrel ip
        JOIN vtiger_crmentity c 
            ON ip.id = c.crmid
        WHERE c.setype = 'Quotes'
    ) AS ranked_records
    WHERE rn <= 100
    GROUP BY productid
) AS product_avg ON p.productid = product_avg.productid
SET p.estimated_price = product_avg.avg_price;

脚本逻辑说明 #

1. 最内层查询：通过ROW_NUMBER()按productid分组，给每条Quotes记录按创建时间倒序编号，确保每个产品的最新100条记录编号为1-100
2. 中间层查询：筛选出编号≤100的记录，按productid分组计算均价
3. 外层关联更新：将均价数据与vtiger_products表关联，批量更新estimated_price字段

可选优化 #

如果需要保留无Quotes记录产品的原estimated_price值，可修改SET语句为：

SET p.estimated_price = COALESCE(product_avg.avg_price, p.estimated_price)

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