MS Access SQL：使用另一个表中的Sum更新连接查询

I am trying to update the "Price" field of a table named "Products" in this example with the "Quantity" field from another table called "OrderDetails. I use t as a temporary table to store my query results from OrderDetails, then INNER JOIN the two tables (p and t). I'm still getting error. I validated the query piece (SELECT ...... GROUP BY ProductID) works. It's the UPDATE that is throwing error. Any thought?

我试图用另一个名为“OrderDetails”的表中的“Quantity”字段更新名为“Products”的表的“Price”字段。我使用t作为临时表来存储来自OrderDetails的查询结果，然后是INNER加入两个表（p和t）。我仍然收到错误。我验证了查询部分（SELECT ...... GROUP BY ProductID）的工作原理。这是UPDATE抛出错误。有什么想法？

    UPDATE p
    SET Price = t.sumQuan
    FROM Products AS p
    INNER JOIN
    (
        SELECT ProductID, SUM(Quantity) sumQuan
        FROM OrderDetails
        GROUP BY ProductID 
    ) t
    ON t.ProductID = p.ProductID;

1 个解决方案

#1

Maybe just a syntax variance with Access vs other RDBMS?

也许只是Access与其他RDBMS的语法差异？

UPDATE products 
INNER JOIN (SELECT ProductID, SUM(Quantity) sumQuan
            FROM OrderDetails
            GROUP BY ProductID 
           ) t
  ON t.ProductID = p.ProductID;
SET Price = t.sumQuan