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 cSET c.TotalProductsCost=c.TotalProductsCost+p.Price
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.
Dan, a couple of thoughts on this. First of all, if you want to get a sum of the prices for each Company (I assume you mean price and not "prize"), then why not use the "SUM" function and GROUP BY Company? You won't get a sum by simply joining the 2 tables.
ReplyDeleteHowever, more importantly, it's unclear why you would ever put a field like "TotalProductsCost" in your "Companies" table. This creates a lot of extra work for you, because you and your developers would now be obligated to constantly update the "TotalProductsCost" field whenever any changes are made to the "Products" table. You are basically building into your design a blatant invitation for inconsistent data.
Doesn't it make more sense to calculate that kind of "sum" data in real-time, perhaps via a view or stored proc?
For example:
SELECT c.CompanyName, 'TotalProductsCost' = ISNULL(SUM(p.Price), 0)
FROM dbo.Companies c
LEFT OUTER JOIN dbo.Products p ON c.CompanyID = p.CompanyID
GROUP BY c.CompanyName
go
CompanyName TotalProductsCost
--------------- ---------------------
A 60.00
B 90.00
--(2 row(s) affected)
Thanks for reading AJ!
ReplyDeleteYou are absolutely right about wrong design.
But I gave this example just for demonstration purpose and it is not a real word example.
I just wanted to demonstrate situation when UPDATE statement behaves differently then Select statement.