January 14, 2008 at 7:23 pm
Posted - 01/14/2008 : 20:18:48
--------------------------------------------------------------------------------
Hi Guys,
Here is my query
UPDATE tblTemp SET fkProgram = (select id from tblProgram WHERE description = t.ProgramBuy) FROM tblTemp t
Here i am just taking the id from the tblprogram and updating the value in the tbltemp based on the description. I am getting the below error. Most of times it works but some times i get this error any idea.? Appreciate your help
Thanks
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.
January 14, 2008 at 7:43 pm
I believe error explains itself.
2 or more ID's have the same description.
Run
[Code]select COUNT(id), description
from tblProgram
GROUP BY description
HAVING COUNT(id) > 1[/Code]
and see which description(s) is an offender.
P.S. You should have unique constraint on the field "description" to prevent such errors in future.
_____________
Code for TallyGenerator
January 14, 2008 at 7:53 pm
You are right, 2 id's have the same description. Thanks for your help
January 15, 2008 at 2:30 am
[Code]UPDATE tblTemp SET fkProgram = (select id from tblProgram WHERE description = t.ProgramBuy) FROM tblTemp t[/Code]
Pardon me for nitpicking, if the alias 't' is assigned in the outer query, how can it be recognised inside the inner query?
January 15, 2008 at 2:47 am
goodguy (1/15/2008)Pardon me for nitpicking, if the alias 't' is assigned in the outer query, how can it be recognised inside the inner query?
All aliases assigned in outer queries, are recognised inside inner queries.
_____________
Code for TallyGenerator
January 15, 2008 at 7:20 am
January 15, 2008 at 2:25 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply