Update query is returning error "Subquery returned more than one value". I need to update the Sellout % for each partners. This is very urgent. Please help
UPDATE dbo.tbl_Non_Verified_Partner SET [HW_PCLW]=(SELECT SUM(A.[Sellout Est Net CLC]) / (select SUM([Sellout Est Net CLC]) from dbo.tbl_FlashProData) from dbo.tbl_FlashProData A inner join dbo.tbl_PLMapping C ON A.[Current Product Line] = C.PL AND C.Category='HW_PCLW' INNER JOIN dbo.tbl_Non_Verified_Partner B ON B.[SYS_ID]=A.[HQ Partner ID] GROUP BY B.[SYS_ID])
The inner query returns as many rows as there are distinct SYS_ID's in the table that satisfy the join condition. If there is more than one, that query returns more than one row and then SQL Server does not know what to do with those - because you are asking it to set one value from a set of multiple values. So depending on what you are trying to accomplish, you will need to either remove the GROUP BY B.[SYS_ID], or modify the query as shown below:
UPDATE p
SET [HW_PCLW] = s.Result
FROM
(
SELECT SUM(A.[Sellout Est Net CLC]) /(
SELECT SUM([Sellout Est Net CLC])
FROM dbo.tbl_FlashProData
) AS Result
FROM dbo.tbl_FlashProData A
INNER JOIN dbo.tbl_PLMapping C
ON A.[Current Product Line] = C.PL
AND C.Category = 'HW_PCLW'
INNER JOIN dbo.tbl_Non_Verified_Partner B
ON B.[SYS_ID] = A.[HQ Partner ID]
GROUP BY
B.[SYS_ID]
) s
INNER JOIN dbo.tbl_Non_Verified_Partner p ON
p.SYS_ID = B.SYS_ID
UPDATE p
SET [HW_PCLW] = s.Result
FROM
(
SELECT B.SYS_ID,SUM(A.[Sellout Est Net CLC]) /(
SELECT SUM([Sellout Est Net CLC])
FROM dbo.tbl_FlashProData
) AS Result
FROM dbo.tbl_FlashProData A
INNER JOIN dbo.tbl_PLMapping C
ON A.[Current Product Line] = C.PL
AND C.Category = 'HW_PCLW'
INNER JOIN dbo.tbl_Non_Verified_Partner B
ON B.[SYS_ID] = A.[HQ Partner ID]
GROUP BY
B.[SYS_ID]
) s
INNER JOIN dbo.tbl_Non_Verified_Partner p ON
p.SYS_ID = s.SYS_ID
Subquery returned more than one value
Posted - 10/14/2012 : 14:39:02
