June 29, 2009 at 2:08 pm
I have a stored procedure which updates a few fields in a table. Simple enough... However, when trying to run this block of code, I get the error in the subject:
update sales_contest_salesman_dan
set AVERAGE_SALE_COUNT =
(
select written_sales_count
from dw_historical_monthly_salesman_sales as hmss, sales_contests sc
where sc.contest_id = 11 and
hmss.commission_month between '5-1-09' and '6-30-09' and
hmss.employee_code = sales_contest_salesman_dan.employee_code
)
The error returned is: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression
Now I know you can use IN or NOT IN, but that won't work... What am I missing here? I know it retunrs more than one value. It's suppose to... But it's suppose to insert THAT value into the appropriate row where the employee_code matches the employee_code from the other table.
Anyway... I'm sure this is silly and a simple thing, but I am STUCK!
Thank you in advance for any help,
Dan
June 29, 2009 at 2:17 pm
First, to help we will need additional information. We need the DDL (CREATE TABLE statements) for the tables involved, both source and destination. Sample data in a readily consummable format (can be cut/paste/run) to load the tables created with you DDL statements. Should be representative of your actually data and have similar issues as you are currently experiencing. Expected results based on the sample data. The fourth item you have already provided in your original post, the code you have written so far.
For more help on these requirements, including how to do it, please read the first article I reference below in my signature block regarding asking for assistance.
June 29, 2009 at 2:19 pm
Take a look in Books Online for the UPDATE ... FROM syntax.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2009 at 2:31 pm
GilaMonster (6/29/2009)
Take a look in Books Online for the UPDATE ... FROM syntax.
Seriously...? That's your help? I have Googled this for a good hour already. Hence why I have come here. I usually come here when I can't figure it out on my own. Are you saying that my syntax is wrong?
June 29, 2009 at 2:34 pm
ddicecca (6/29/2009)
GilaMonster (6/29/2009)
Take a look in Books Online for the UPDATE ... FROM syntax.Seriously...? That's your help? I have Googled this for a good hour already. Hence why I have come here. I usually come here when I can't figure it out on my own. Are you saying that my syntax is wrong?
Actually, I have to agree with Gail. That's why I asked for the information I did, so that we could provide you with additional help. Of course, if you don't want our help...
June 29, 2009 at 2:40 pm
Lynn Pettis (6/29/2009)
ddicecca (6/29/2009)
GilaMonster (6/29/2009)
Take a look in Books Online for the UPDATE ... FROM syntax.Seriously...? That's your help? I have Googled this for a good hour already. Hence why I have come here. I usually come here when I can't figure it out on my own. Are you saying that my syntax is wrong?
Actually, I have to agree with Gail. That's why I asked for the information I did, so that we could provide you with additional help. Of course, if you don't want our help...
I'm sorry... I'm not an SQL expert by any means, however, to me, this seems like a pretty straight forward UPDATE statement. And I am trying to update a field in my table that is a varchar(3) field. Nothing more... nothing less...
I guess I will have to look more into generating table scripts and getting sample data - et cetera...
I thought that perhaps someone would see something out of the ordinary, since I don't see anything any different than from my other UPDATE blocks in my SP. It's all the same. This is the only one giving me a problem, which is odd...
June 29, 2009 at 2:45 pm
ddicecca (6/29/2009)
GilaMonster (6/29/2009)
Take a look in Books Online for the UPDATE ... FROM syntax.Seriously...? That's your help?
I'm sorry, I thought you might like a hint where to look so you could resolve the problem instead of waiting maybe a couple more hours for someone to rewrite the query.
Are you saying that my syntax is wrong?
No. I'm saying there's a second form to the update statement. If you want me to write the code for you, please post the sample data, table definitions as Lynn asked and I'll get on it tomorrow. (it's 11pm where I'm sitting)
p.s. There's a cross join in your subquery. Sure that isn't the source of the problem?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2009 at 2:48 pm
ddicecca (6/29/2009)
Lynn Pettis (6/29/2009)
ddicecca (6/29/2009)
GilaMonster (6/29/2009)
Take a look in Books Online for the UPDATE ... FROM syntax.Seriously...? That's your help? I have Googled this for a good hour already. Hence why I have come here. I usually come here when I can't figure it out on my own. Are you saying that my syntax is wrong?
Actually, I have to agree with Gail. That's why I asked for the information I did, so that we could provide you with additional help. Of course, if you don't want our help...
I'm sorry... I'm not an SQL expert by any means, however, to me, this seems like a pretty straight forward UPDATE statement. And I am trying to update a field in my table that is a varchar(3) field. Nothing more... nothing less...
I guess I will have to look more into generating table scripts and getting sample data - et cetera...
I thought that perhaps someone would see something out of the ordinary, since I don't see anything any different than from my other UPDATE blocks in my SP. It's all the same. This is the only one giving me a problem, which is odd...
For your reading pleasure, you may want to look at this as well: The Flip Side
To put it simply, you know your data and data structures, we don't. To really help you, we have to understand them as well to some extent. With the DDL, sample data, and expected results we can provide you with test code.
June 29, 2009 at 2:50 pm
GilaMonster (6/29/2009)
ddicecca (6/29/2009)
GilaMonster (6/29/2009)
Take a look in Books Online for the UPDATE ... FROM syntax.Seriously...? That's your help?
I'm sorry, I thought you might like a hint where to look so you could resolve the problem instead of waiting maybe a couple more hours for someone to rewrite the query.
Are you saying that my syntax is wrong?
No. I'm saying there's a second form to the update statement that. If you want me to write the code for you, please post the sample data, table definitions as Lynn asked and I'll get on it tomorrow. (it's 11pm where I'm sitting)
p.s. There's a cross join in your subquery. Sure that isn't the source of the problem?
Actually Gail, I think it is just an old style inner join. Won't know until the DDL for the tables are posted.
June 29, 2009 at 2:53 pm
Just a hint.
The WHERE clause you have coded is entirely within the subquery. So there is no WHERE clause that applies to the UPDATE action. Every row in the table will be updated. But SQL Server doesn't know what value to use in updating the rows because the subquery returns more than 1 value and it doesn't know wheter you want to use the first one or the last one or some other one. Based on your comments, I suspect this is not what you want.
If you look up the syntax for UPDATE FROM in BOL, as suggested, you'll see how this is different than your other updates.
June 29, 2009 at 2:54 pm
Lynn Pettis (6/29/2009)
Actually Gail, I think it is just an old style inner join.
Except that there's no condition in the where clause that joins the two tables either. It's fine if one of those tables has only 1 row, if not ..... Don't know for sure either way though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2009 at 2:56 pm
GilaMonster (6/29/2009)
Lynn Pettis (6/29/2009)
Actually Gail, I think it is just an old style inner join.Except that there's no condition in the where clause that joins the two tables either. It's fine if one of those tables has only 1 row, if not ..... Don't know for sure either way though.
Your right, what I was looking at may actually be the correlated subquery.
Definately a syntax error based on what i just saw.
June 29, 2009 at 3:00 pm
When you refer to Books On Line, is that something on this forum? I apologize ahead of time if this is a stupid question...
June 29, 2009 at 3:02 pm
Try this:
select hmss.employee_code
from dw_historical_monthly_salesman_sales as hmss, sales_contests sc
where sc.contest_id = 11 and
hmss.commission_month between '5-1-09' and '6-30-09'
group by hmss.employee_code
having count(*) > 1
That'll at least tell you which record(s) are causing the multiple rows. For each employee_code that comes up in that, you'll have to work out how to pick the specific written_sales_count that you want.
That's what's causing the error.
I can't actually suggest a means of fixing it without a better idea of how the tables work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 29, 2009 at 3:03 pm
ddicecca (6/29/2009)
When you refer to Books On Line, is that something on this forum? I apologize ahead of time if this is a stupid question...
Nope, Books Online is the SQL Server Help System. You can access from SSMS (SQL Server Management Studio) by pressing the {f1} function key.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply