How does this update query set the sum when it has a joins

user_12720807 0
Viewed 50
UPDATE
Chemicals.dbo.ChmProductCompositions 

SET Vhap = SUM(PercentOfProduct)

FROM 
Chemicals.dbo.ChmProductCompositions  PC
JOIN Chemicals.dbo.ChmCompositionChemicals CC ON CC.ProductCompositionID = pc.ProductCompositionID
JOIN Chemicals.dbo.ChmChemicals C ON C.ChemicalID = CC.ChemicalID
WHERE IsHazardous = 1

I have the query above and I was wondering does something like the above query get the total sum and set it or the sum for each specific and set it?

I was also wondering if it gets the total sum and sets each one to the total how might I go about getting the sum for each individual item and then set it to that?

aggregate sql-server sql
2 Answers

It doesn't. Just try the query -- it is totally safe, because it returns an error. UPDATEs are not aggregations, so SUM() is not allowed.

You need to aggregate before joining:

UPDATE pc
    SET Vhap = cc.PercentOfProduct
    FROM Chemicals.dbo.ChmProductCompositions pc JOIN
         (SELECT cc.ProductCompositionID, SUM(PercentOfProduct) as PercentOfProcue
          FROM Chemicals.dbo.ChmCompositionChemicals CC JOIN
               Chemicals.dbo.ChmChemicals C
               ON C.ChemicalID = CC.ChemicalID
          GROUP BY cc.ProductCompositionID
         ) cc
         ON CC.ProductCompositionID = pc.ProductCompositionID
    WHERE IsHazardous = 1;

Note that IsHazardous may belong in the subquery. Your question is not clear.

user_11440350

You have two levels of the problem. First, you need to perform an aggregation. Next, you need to update the corresponding record for each record in the aggregates. You can perform this as Gordon Linoff described in a subquery, or, you can use temporary tables instead. Let's assume that you have a table

pc_temp(ProductCompositionID, result)

insert-select:

INSERT INTO pc_temp(ProductCompositionID, result)
SELECT PC.ProductCompositionID, SUM(PercentOfProduct)
FROM 
Chemicals.dbo.ChmProductCompositions  PC
JOIN Chemicals.dbo.ChmCompositionChemicals CC ON CC.ProductCompositionID = pc.ProductCompositionID
JOIN Chemicals.dbo.ChmChemicals C ON C.ChemicalID = CC.ChemicalID
WHERE IsHazardous = 1

So you end up with records in the temp table that can be used in your simplified update because the aggregation already happened.

simplified update:

UPDATE PC 
SET Vhap = result
FROM 
Chemicals.dbo.ChmProductCompositions  PC
JOIN pc_temp
ON PC.ProductCompositionID = pc_tmp.ProductCompositionID;
user_4365600
Related