聚合不应出现在 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

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

相关推荐