An Update query that doesn`t work

Why the following UPDATE doesn`t work, I asked my colleague.
And did not get any answer.

Consider the following example, you have a table that holds
companies and a table that holds products per company.

SELECT  *
FROM    dbo.Companies

SELECT *

FROM   dbo.Products

What I want to do is to update the "TotalProductsCost"

in the " Companies" table by the following query:

UPDATE c
SET         c.TotalProductsCost=c.TotalProductsCost+p.Price

FROM    dbo.Companies as c
INNER JOIN dbo.Products as p ON c.CompanyID = p.CompanyID

If we fetch the row from the "Companies" table, we will get:

As you can see the update statement did not work as I thought
it would. The Update statement took only the first row
(only one product) of each company and not all products.

What I thought is, the Update statement will go over row by row
and take all products of each company as the Select statement below:

SELECT c.CompanyName,p.ProductID,p.Price
FROM    dbo.Companies as c
INNER JOIN dbo.Products as p ON c.CompanyID = p.CompanyID

If you have an explanation, I will be glad to get it.

Tweet