SQL Server - 从一个表中选择多行数据并使用第一个选定数据更新另一个表上的多行数据

I am going to explain it as simple as possible. Now I have a table_1 which has price and itemId. I also have another table table_2 which has totalprice,itemId.

我将尽可能简单地解释它。现在我有一个table_1,它有price和itemId。我还有另一个表table_2,它有totalprice,itemId。

What I want to do is to sum all price from table_1 grouped by itemId and update that sum of price in table_2 with itemId key as the common column.

我想要做的是汇总tableI分组的table_1的所有价格,并将table_2中的价格总和更新为itemId键作为公共列。

The price per itemId summed from table_1 should update the totalprice column in table_2.

从table_1求和的每个itemId的价格应该更新table_2中的totalprice列。

Is that possible ? Thank you.

那可能吗 ?谢谢。

SQL Server 2008 R2

1 个解决方案

#1

Yes is it possible. You can do like this:

是的,这是可能的。你可以这样做:

update T2
set totalprice = T1.totalprice
from Table_2 as T2
  inner join (select sum(price) as totalprice,
                     itemid
              from Table_1
              group by itemid) as T1
    on T2.itemid = T1.itemid

http://data.stackexchange.com/*/q/119388/

If you don't already have all itemid's in table_2 you can use a merge to update the existing rows and add a new row if it is missing.

如果您还没有table_2中的所有itemid,则可以使用合并来更新现有行,如果缺少则添加新行。

merge Table_2 as T2
using (select sum(price) as totalprice,
              itemid
       from Table_1
       group by itemid) as T1
on T1.itemid = T2.itemid
when matched then 
  update set totalprice = T1.totalprice
when not matched then 
  insert (totalprice, itemid) 
  values(T1.totalprice, T1.itemid);

http://data.stackexchange.com/*/q/119389/

#1