Make update using join

user_10449756 0
Viewed 65

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.

sql-server-2008 sql-server sql
4 Answers

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;
user_58413060

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;
user_64266920

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
user_102491520

This Will Answer You Question 

            Create table #Customermaster
                (CustomerID int,DOY Money)

            Create table #History
                (CustomerID int,Price Money,RestOfPrice money)

            Insert into #Customermaster(CustomerID) Values (1),(2)
            Insert into #History                    Values (1,100,50),(2,200,50),(1,300,50)

            Select * from #Customermaster
            Select * from #History

            Update t1 
            Set T1.DOY = t2.D
            From #Customermaster t1 join
            (Select T2.CustomerID,isnull(Sum(t2.Price),0)- isnull(Sum(t2.RestOfPrice),0) as D 
            from #History T2 Join #Customermaster T1 On T1.CustomerID=T2.CustomerID Group By T2.CustomerID) t2
            On T1.CustomerId=T2.CustomerID;


            Select * from #Customermaster
            Select * from #History

Here Is the two tables before you update them

And Here is the Results

Hope that Would Help You

user_96615230
Related