MS Access SQL: Update Join Query with Sum from Another Table

Question

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?

    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;

Possible duplicate of Access DB update one table with value from another — xQbert
– xQbert, Commented Feb 16, 2017 at 22:09
@xQbert The error I received is Syntax error in UPDATE statement which I cannot figure out. I read a few topics around this on Stackoverflow and I still don't know where I did wrong. If someone can help me with some pointers would be great. — NinjaWarrior
– NinjaWarrior, Commented Feb 17, 2017 at 0:14

xQbert · Accepted Answer · 2017-02-16 22:08:29Z

2

Maybe just a syntax variance with Access vs other 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

answered Feb 16, 2017 at 22:08

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MS Access SQL: Update Join Query with Sum from Another Table

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related