Update multiple columns of a table using aggregate values from another table

sql, sql-server

Solution

The correct syntax in MySQL:

Update temp1  t1 join
       (select p_id, SUM(t2.Margin2) as margin2, SUM(t2.Revenue2) as revenue2,
               SUM(t2.Sales2) as sales2
        from tempcost t2
        group by p_id
       ) t2
       on t1.P_Id = t2.P_Id
    set t1.Margin = t2.margin2,
     t1.Revenue = t2.Revenue2,
     t1.Sales = t2.Sales2;

The correct syntax in SQL Server:

Update t1 
    set Margin = t2.margin2,
        Revenue = t2.Revenue2,
        Sales = t2.Sales2
    from temp1 t1 join
         (select p_id, SUM(t2.Margin2) as margin2, SUM(t2.Revenue2) as revenue2,
               SUM(t2.Sales2) as sales2
          from tempcost t2
          group by p_id
         ) t2
         on t1.P_Id = t2.P_Id;

Problem

I have 2 tables,i am trying to update first using the aggregate functions on 2nd table.Below is the code :- ``` Update temp1 t1 set t1.Margin = SUM(t2.Margin2), t1.Revenue = SUM(t2.Revenue2), t1.Sales = SUM (t2.Sales2), t1.Revenue = SUM (t2.Revenue2) from t1 inner join tempcost t2 on t1.P_Id = t2.P_Id ``` Shows the error "An aggregate may not appear in the set list of an UPDATE statement". Any suggestion on how to achieve this.

Original source