怎么做这个更新声明 [英] how can do this update statement

问题描述

update Products_TBL set Product_Price=(
SELECT sum(rn.Amount) / sum(rn.Quantity) AS Result
FROM  Receipt_NoteDetalisTBL as rn
GROUP BY Product_Id
having rn.Product_Id between 1 and 1500

)
where Product_Id between 1 and 1500

this  sql erorr ;
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

推荐答案

您好请使用此查询为您提供所需的输出，即此查询将在循环中更新productid 1到1500的行。





提前致谢。

Hi please use this query which give you your required output ie, this query will update rows from productid 1 to 1500 in a loop.


Thanks in advance.

DECLARE @a INT 

SET @a = 1

WHILE @a < 1501
BEGIN

exec sp_executesql N'update Products_TBL set Product_Price=(
SELECT sum(rn.Amount) / sum(rn.Quantity) AS Result
FROM Receipt_NoteDetalisTBL as rn
GROUP BY Product_Id
having rn.Product_Id=@a) where Product_Id=@a'   

SET @a = @a + 1
END

这里的错误信息是一个很大的线索：查询返回多个值 - 在给定GROUP子句的情况下，您期望 - 并且您尝试插入将几个不同的值组合成一行。这是行不通的。



我不确定你到底想要做什么，但是单向回合可能只是返回你的TOP 1结果内部选择。

The error message is a big clue here: the query returns more than one value - which you'd expect, given the GROUP clause - and you are trying to insert several different values into a single row. That won't work.

I'm not sure exactly what you are trying to do, but one way round might be to return just the TOP 1 result from your inner select.

这篇关于怎么做这个更新声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！