聚合不应出现在 UPDATE 语句的集合列表中

修改语句：

update A set WZCount=ISNULL(WZCount,0)+(select SUM(WZCount) from T_PM_OutStock_SUB where MaterialID =A.MaterialID and _MainID='{_AutoID}') from T_PM_MaterialStock A,T_PM_OutStock_SUB

系统报错：

聚合不应出现在 UPDATE 语句的集合列表中

问题：

修改语句中不能出现聚合函数这一类的

解决问题：

那就把聚合函数放到select 查询与查出来当成一个数据表   再用数据表和原先的数据表匹配 进行添加

修改之后;

UPDATE T_PM_MaterialStock
SET WZCount = WZCount+TB.ALLCount
FROM (SELECT
b.MaterialID,SUM(b.Count) AS ALLCount
FROM
T_PM_MaterialStock a
INNER JOIN
T_PM_OutStock_SUB b
ON a.MaterialID = b.MaterialID
WHERE b._MainID='{_AutoID}'
GROUP BY
b.MaterialID
) AS TB
WHERE
T_PM_MaterialStock.MaterialID = TB.MaterialID

正常示例：

UPDATE    库存表2
SET              库存数量 = TB.剩余数量
FROM  (SELECT 
      入库表.条形码,SUM(入库表.剩余数量) AS 剩余数量
FROM  
      库存表2 
      INNER JOIN
      入库表 
ON 库存表2.条形码 = 入库表.条形码 
      GROUP BY
      入库表.条形码
      ) AS TB 
WHERE
      库存表2.条形码 = TB.条形码
另外一个语句写的例子：

UPDATE T_PM_MaterialEntry_SUB
SET Surplus = ISNULL(Surplus,0)+TB.ALLCount
FROM (SELECT
b.SourceNumID,SUM(b.Count) AS ALLCount
FROM
T_PM_MaterialEntry_SUB a
INNER JOIN
T_PM_OutStock_SUB b
ON a._AutoID = b.SourceNumID
WHERE b._MainID='{_AutoID}'
GROUP BY
b.SourceNumID
) AS TB
WHERE
T_PM_MaterialEntry_SUB._AutoID = TB.SourceNumID

转载于:https://www.cnblogs.com/zhangwei99com/p/8522138.html