CODEWITHSUNDEEP
sqlsql-server
Shivam657
Shivam657

Reputation: 743

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

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.

Upvotes: 3

Views: 4900

Answers (2)

bhamby
bhamby

Reputation: 15450

How about moving it into a subquery?

Update temp1  t1
    set t1.Margin = t2.Margin
       ,t1.Revenue = t2.Revenue
       ,t1.Sales = t2.Sales2
    from t1
    join (
        SELECT
             SUM(Margin2) as Margin
            ,SUM(Revenue2) as Revenue
            ,SUM(Sales2) as Sales
        FROM tempcost
    ) t2
    on t1.P_Id = t2.P_Id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270081

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;

Upvotes: 6

Related Questions