聚合可能不会出现在UPDATE语句的集合列表中。 [英] An aggregate may not appear in the set list of an UPDATE statement.

问题描述

我正在尝试编写一个查询，用于更新我在购买或销售时所拥有的所有库存物品的数量。

我收到以下错误

聚合可能不会出现在UPDATE语句的设置列表中



什么我试过了：

I'm trying to write a query that updates quantity on hand for all the Inventory Items that i have whenever i make a purchase or a sale.
I get the following error
An aggregate may not appear in the set list of an UPDATE statement

What I have tried:

UPDATE p
SET p.QuantityOnHand =
    (ISNULL(SUM(ISNULL(b.Qty, 0)), 0) -
     ISNULL(SUM(ISNULL(i.Qty, 0)), 0) -
     ISNULL(SUM(ISNULL(s.Qty, 0)), 0))
FROM Products p

INNER JOIN BillDetails b ON p.ProductId = b.Pid
INNER JOIN InvoiceDetails i ON p.ProductId = i.Pid
INNER JOIN SalesDetails s ON p.ProductId = s.Pid

推荐答案

问题很明显......你做不到。请尝试使用子查询。

此处示例... 包含聚合的更新语句在SQL服务器中不起作用 - Stack Overflow [ ^ ]

The problem is quite clear...you can't do that. Try using a sub-query instead.
Example here...Update statement containing aggregate not working in SQL server - Stack Overflow[^]

这篇关于聚合可能不会出现在UPDATE语句的集合列表中。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！