SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Subquery returned more than one value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sathyarangaraj
Starting Member

India
11 Posts

Posted - 10/14/2012 :  14:39:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/14/2012 :  15:02:19  Show Profile  Reply with Quote
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
Go to Top of Page

sathyarangaraj
Starting Member

India
11 Posts

Posted - 10/14/2012 :  15:16:35  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/14/2012 :  15:17:50  Show Profile  Reply with Quote
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
Go to Top of Page

sathyarangaraj
Starting Member

India
11 Posts

Posted - 10/14/2012 :  15:20:43  Show Profile  Reply with Quote
Hi....

Its showing invalid column name if i put s.sys_id
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/14/2012 :  15:23:27  Show Profile  Reply with Quote
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
Go to Top of Page

sathyarangaraj
Starting Member

India
11 Posts

Posted - 10/14/2012 :  15:41:54  Show Profile  Reply with Quote
Thank you so much Sunita. Its getting updated now
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/14/2012 :  15:53:57  Show Profile  Reply with Quote
Glad it worked out! Sorry about the mishaps along the way.
Go to Top of Page

sathyarangaraj
Starting Member

India
11 Posts

Posted - 10/14/2012 :  23:27:17  Show Profile  Reply with Quote
Hi Sunita,

When i run the query its showing "no of rows affected".. but while checking its showing null. Please help me
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/15/2012 :  06:49:06  Show Profile  Reply with Quote
Does the inner query (shown below) return any rows at all? I suspect not - and if it does not, you need to figure out why.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000