sql - 使用来自另一个表的聚合值更新表的多个列

Question

我有 2 个表，我试图首先使用第二个表上的聚合函数进行更新。下面是代码:-

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

显示错误“聚合可能不会出现在更新语句的集合列表中”。关于如何实现这一目标的任何建议。

Answer 1

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;

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;