Subquery returned more than one value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Subquery returned more than one value

Author

Topic

sathyarangaraj
Starting Member

11 Posts

Posted - 2012-10-14 : 14:39:02

Hi All,

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])

Thanks,
Sathya

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-14 : 15:02:19

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

sathyarangaraj
Starting Member

11 Posts

Posted - 2012-10-14 : 15:16:35

Hi..

Thanks for your reply. I am getting an error in the end P.[SYS_ID]=B.[SYS_ID] as "The multi-identifier "B.SYS_ID" could not be found in your query.

please help me to get this

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-14 : 15:17:50

quote:
Originally posted by sathyarangaraj

Hi..

Thanks for your reply. I am getting an error in the end P.[SYS_ID]=B.[SYS_ID] as "The multi-identifier "B.SYS_ID" could not be found in your query.

please help me to get this

Oops, my bad. The last part should be:

) s
   INNER JOIN dbo.tbl_Non_Verified_Partner p ON
	p.SYS_ID = s.SYS_ID

sathyarangaraj
Starting Member

11 Posts

Posted - 2012-10-14 : 15:20:43

Hi....

Its showing invalid column name if i put s.sys_id

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-14 : 15:23:27

Must be a bad day for me :) See in red below

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