help? - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

help?

Author

Topic

krish001
Yak Posting Veteran

61 Posts

Posted - 2009-12-26 : 23:20:13

Iam getting error subquery returned more than one value??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-26 : 23:31:19

Well it isn't allowed, so show us your query and will help you rewrite it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

krish001
Yak Posting Veteran

61 Posts

Posted - 2009-12-27 : 01:59:26

quote:
Originally posted by krish001

Iam getting error subquery returned more than one value??

set @id=(select id from table where branch='b')
select coloumn from table where id=@id

when iam running this iam getting sub query error how pass id onfly??

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-27 : 12:05:18

This is because in your table are more than one id where branch='b'.
You variable @id can only take one value hence the error message.
You can do this instead (only one example, I don't know your tables and so on):

select column from table where id in (select id from table where branch='b')

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-28 : 00:48:28

If both tables refer a same table

select column from table where branch='b'

Madhivanan

Failing to plan is Planning to fail

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-12-28 : 02:20:35

For cross the error
exchange SET to SELECT (assignment select)

select @id=id from table where branch='b'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-28 : 04:10:24

quote:
Originally posted by ms65g

For cross the error
exchange SET to SELECT (assignment select)

select @id=id from table where branch='b'

There is a problem with this. If there are more than one id returned, only the last id will be assigned to @id

Madhivanan

Failing to plan is Planning to fail

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-12-28 : 09:28:12

quote:
Originally posted by madhivanan

quote:
Originally posted by ms65g

For cross the error
exchange SET to SELECT (assignment select)

select @id=id from table where branch='b'

There is a problem with this. If there are more than one id returned, only the last id will be assigned to @id

Madhivanan

Failing to plan is Planning to fail

I know. It is just a way to crossing the error and nothing else.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-12-28 : 11:37:26

ON ERROR RESUME NEXT is NOT your friend

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources