error : subquery returned more than 1 value

Question

Post reply

error : subquery returned more than 1 value

HBK_4700

SSCrazy Eights

Points: 8932
More actions
July 6, 2011 at 2:11 pm

#239197

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I am trying pull all the values for each user

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

The error message usually include a line number to point you in the right direction.

It can be any of the three updates using a subquery.

The TOP 1 ORDER BY will prevent to have more than one row returned. If not, there might be two or more return values. Which one of those do you want to see in your target column? SQL Server won't know so you need to help the system to come up with a single value.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 2

LutzM (7/6/2011)
The error message usually include a line number to point you in the right direction.
It can be any of the three updates using a subquery.
The TOP 1 ORDER BY will prevent to have more than one row returned. If not, there might be two or more return values. Which one of those do you want to see in your target column? SQL Server won't know so you need to help the system to come up with a single value.

Hi LutzM,

Thanks for your reply.

Acutally I am trying to pull all the records. So need help to update the script

LutzM SSC Guru Points: 107049 More actions · Answer 3

Please post table def for the tables invloved together with some sample data in a ready to use format as described in the first link in my signature.

Please include your expected result based on the sample.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 4

laddu4700 (7/6/2011)
Acutally I am trying to pull all the records. So need help to update the script

I suspect that's the problem right there. If you're trying to pull all of the records, then the chances are that's more than one... but you are using an operation that only allows one record.

What is it you are doing with the records you are pulling?

thundersplash845 SSC Eights! Points: 957 More actions · Answer 5

Try using DISTINCT...

Using this command will eliminate the duplicate records.

Eg : Select distinct id,name from table1

Thanks

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

thundersplash845 (7/6/2011)
Try using DISTINCT...
Using this command will eliminate the duplicate records.
Eg : Select distinct id,name from table1
Thanks

This isn't a solution. Note OP is using this in a subquery. only ONE record can be returned - DISTINCT will not guarantee one row, just that all rows returned (0-N of them) will be unique.

OP - we cannot help you without code and table defs. But the solution is one that must guarantee 0 or 1 row will be returned from the part of your statement that is throwing the error. Or you refactor the code to remove this construct completely.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service