如何在update语句中使用聚合函数 [英] how to use aggregate function in update statement

问题描述

我有两张桌子， itemmaster 和 stockdetails 如下：

itemmaster：

 itemid availableqty 
  100   0  
  101   0  
  102   0  
  103   0  
  104   0  
  105   0  

stockdetails：

 itemid availablebelqty branch 
  101  < span class =code-digit> 20   1  
  101   30   2  
  101   30   3  
  102   90   1  
  102   80   2  
  102   20   3  
  102   30   4  
  103   40   1  
  103   30   4  
  103   50   2  
  103   70   3  

i想要更新< b> itemmaster 表格中所有 stockdetails 表的总和 availablebelqty ...



有任何单个quries来更新表格数据吗？

执行查询后 itemmaste 表应该像这样更新

 itemid availableqty 
  100   0  
  101   80  
  102   220  
  103   190  
  104   0  
  105   0  

i尝试使用此查询

 更新 tblitemmaster  set  availableqty =（ select  sum（availableqty）来自 stockdetails 其中 itemid = 110）
 其中 ItemID = 102 

但这只是在一次记录时更新。我想更新所有记录。有任何quries更新该表吗？



任何人请帮助我。

解决方案

尝试这样的事情：

 更新 t1 
  SET  t1.avaliblequality = t2.avaliblequality 
  FROM  itemmaster  AS  t1  INNER   JOIN （
  SELECT  itemid，SUM（avaliblequality） AS  avaliblequality 
  FROM  stockdetails 
  GROUP   BY  itemid 
） AS  t2  ON  t1.itemid = t2 .itemid 

I have two tables,itemmaster and stockdetails as follows:
itemmaster:

itemid  availableqty
100        0
101        0
102        0
103        0
104        0
105        0

stockdetails:

itemid   availabelqty  branch
101  	    20  	 1
101 	    30	         2
101         30  	 3
102         90           1
102         80           2
102         20           3
102         30           4
103         40           1
103         30           4
103         50           2
103         70  	 3

i want to update itemmaster table from sum of all stockdetails table availabelqty...

Have any single quries to update that table data's?
after the execution of queries the itemmaste table should be updated like this

itemid  availableqty
100     0
101	80
102	220
103	190
104	0
105	0

i tried this using this query

update tblitemmaster set availableqty=(select sum(availableqty) from stockdetails where itemid =110)
where ItemID =102

but this is updating at time only one record.I want to update all record.Have any quries to update that table?

Any one please help me.

解决方案

Try something like this:

UPDATE t1
    SET t1.avaliblequality = t2.avaliblequality
FROM itemmaster AS t1 INNER JOIN (
    SELECT itemid, SUM(avaliblequality) AS avaliblequality
    FROM stockdetails
    GROUP BY itemid
    ) AS t2 ON t1.itemid = t2.itemid

这篇关于如何在update语句中使用聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！