Friday, February 26, 2010

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.

2 comments:

  1. 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.

    However, 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)

    ReplyDelete
  2. Thanks for reading AJ!
    You 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.

    ReplyDelete