SQL Average of Last 100 records

Question

In the query I provided below, I am trying to update the field estimated_price of all products with its relevant average: the average price of the newest 100 records of this product in inventory where the type of entity is "Quotes".

I wrote this script but I encountered the error "Unknown column 'vtiger_products.productid' in 'where clause'"

update `vtiger_products` set `vtiger_products`.`estimated_price` =
    (select avg(`subquery`.`unit_price`)
     from
         (select vtiger_inventoryproductrel.listprice as 'unit_price'
          from `vtiger_inventoryproductrel`
          left join `vtiger_crmentity` on `vtiger_inventoryproductrel`.`id` = `vtiger_crmentity`.`crmid`
          where `vtiger_crmentity`.`setype` = 'Quotes'
          and vtiger_inventoryproductrel.productid = vtiger_products.productid
          order by vtiger_crmentity.createdtime desc
          limit 100) as `subquery`
     )

I will be glad if someone helps me to find solution

Answer 1

You can use a joined update for this. And combine it with ROW_NUMBER to get the top 100 rows per productid.

Also, because the subqueries are now not correlated, you can put them in into CTEs to make them more readable.

with numbered as (
    select
      ipr.productid,
      ipr.listprice,
      ROW_NUMBER() OVER (PARTITION BY ipr.productid ORDER BY crm.createdtime DESC) as rn
    from vtiger_inventoryproductrel ipr
    join vtiger_crmentity crm on ipr.id = crm.crmid
    where crm.setype = 'Quotes'
),
averaged as (
    select
      n.productid,
      avg(n.listprice) as avg_price
    from numbered n
    group by
      n.productid
)
update
    vtiger_products p
    join averaged a on a.productid = p.productid and a.rn <= 100
set p.estimated_price = a.avg_price;