sql server programming: Subquery returned more than 1 value -- sql server programming

Subquery returned more than 1 value

Jon
9/12/2003 7:02:15 PM

sql server programming:

Hi, need a little help here. When I am trying to update
a field using the set command and have a select statement
using a inner join or even just using the Where clause to
join, I may sometimes get the message: Subquery
returned more than 1 value...... Now I preume that that
error comes from the fact that one or both of the joined
tables has a duplicate record. Maybe I am wrong. But is
there a way to correct this and avoid the same problem in
the future? Thanks for all your help.

Re: Subquery returned more than 1 value

Jon
9/12/2003 8:29:16 PM

Ok fair enough here is the set command I use. But first
this is a match from two tables were I had not expected
to do so. And these dups are valid.

Table1 has AcctNumber and TypeIndicator.

Table2 has all the data that needs the type indicator
from Table1.

update Table2
set TypeIndicator = (select TypeIndicator from Table1
where table1.acctnumber = table2.acctnumber)

Table2's acctnumber may or may not be duped but that is
something I can't avoid.

Now I am open to other ways of doing this.

Thanks for all your help.

[quoted text, click to view]

Re: Subquery returned more than 1 value

Aaron Bertrand [MVP]
9/12/2003 10:32:52 PM

[quoted text, click to view]

Can you show us some of these examples? We can't fix what we can't see...

Re: Subquery returned more than 1 value

Andrew John
9/13/2003 1:02:40 PM

Jon,

The error says it all - If you want to assign the output of a subquery to a
variable using set, then you need to ensure that only 1 row is returned from
the subquery.

You can kludge this with max, min or top 1 in the subquery, but it in my
experience fixing the root cause is cleaner - Why does the query return
multiple rows when you don't expect it to ? Are you using a proper primary key ?
Do you have valid DRI? ( declaritive referential integrity = constraints, keys, indexes )

As Aaron Bertrand said - If you want specific help then you need to supply
specific data - Table structures ( in DDL, not just a fuzzy description ),
sample data ...

Regards
AJ

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.