Make update using join

Question

I'm trying to make an update, and here is my query

UPDATE t1
SET DOY = isnull(Sum(t2.Price),0)- isnull(Sum(t2.RestOfPrice),0) 
FROM customermaster t1 INNER JOIN History t2 
     ON t1.CustomerID = t2.CustomerID

The query throw an error

An aggregate may not appear in the set list of an UPDATE statement.

Answer 1

You need to use a subquery or a CTE

Using a SubQuery:

UPDATE t1
SET DOY =  K.Res
FROM customermaster t1
INNER JOIN 
(
    SELECT T2.CustomerID, 
           ISNULL(SUM(t2.Price), 0) - ISNULL(SUM(t2.RestOfPrice), 0) Res
    FROM History t2
    GROUP BY T2.CustomerID
) K ON t1.CustomerID = K.CustomerID;

Using a CTE:

WITH CTE AS
(
    SELECT T.CustomerID, 
           ISNULL(SUM(T.Price), 0) - ISNULL(SUM(T.RestOfPrice), 0) Res
    FROM History T
    GROUP BY T.CustomerID
)
UPDATE customermaster 
SET DOY = CTE.Res
WHERE CustomerID = CTE.CustomerID;

Answer 2

I think the following may help :

UPDATE t1
   SET t1.doy = t2.PriceDiff  
  FROM customermaster t1 
 INNER JOIN 
      ( SELECT isnull(sum(Price),0)- isnull(sum(RestOfPrice),0) as PriceDiff, CustomerID 
          FROM History
         GROUP BY CustomerID
      ) as t2 
    ON t1.CustomerID = t2.CustomerID;

Answer 3

You should try something like this:

 update customermaster
     SET customermaster.DOY = final.result
     from 
     (Select t1.CustomerID,(isnull(Sum(t2.Price),0)- isnull(Sum(t2.RestOfPrice),0))as result
    from customermaster t1
     INNER JOIN History t2 
     ON t1.CustomerID = t2.CustomerID) final
     where customermaster.CustomerID=final.CustomerID