Update single row in table from multi rows in another table 1

[jboo]

I have 2 tables: Products and Inventory. The Products rows hold an InventoryTotal, which should be a "sum" of Inventory.LocationTotal (for each Product). Can I do a simple query to update the Products.InventoryTotal field with the total of the fields Inventory.LocationTotal by ProductCode? It seems so simple, but it's driving me mad!

 

[rac2]

Have you tried this -

UPDATE Products
  SET InventoryTotal = (SELECT SUM(LocationTotal)
     FROM Inventory
        WHERE Inventory.ProductID = Products.ProductID)
FROM Products, Inventory