"Subquery returned more than 1 value" with IN()

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)

"Subquery returned more than 1 value" with IN()

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2011-02-11 : 07:12:06

I'm getting this error while I'm trying to use IN clause with UPDATE:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

This works:

declare @idList table
(
	id int
);

insert into @idList
select id1 from MyTable1

select * from MyTable2
where id2 in(
	select id from @idList
)

This doesn't:

declare @idList table
(
	id int
);

insert into @idList
select id1 from MyTable1

update MyTable2
set column = 1
where id2 in(
	select id from @idList
)

Neither does this:

declare @idList table
(
	id int
);

insert into @idList
select id1 from MyTable1

update MyTable2
set MyTable2.column = 1
from MyTable2
inner join @idList i
on MyTable2.id2 = i.id

Any idea what I am doing wrong?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-11 : 07:20:20

That error isn't thrown with those queries. Is there an insert/update trigger on MyTable2?

Poor planning on your part does not constitute an emergency on my part.

Sachin.Nand

2937 Posts

Posted - 2011-02-11 : 07:24:57

Whats the ratio of similar values between @Idlist.Id and MyTable2.Id ? Is it "1 to 1" or "1 to n" or "n to 1" ?

If its "1 to n" or "n to 1" then you will get the error ?

Post your table structure and some sample data.

PBUH

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-11 : 07:28:08

is it the Code which if you run, throwing the specified error? Or you have made any changes while posting it here?

kensai
Posting Yak Master

172 Posts

Posted - 2011-02-11 : 07:33:20

quote:
Originally posted by dataguru1971

That error isn't thrown with those queries. Is there an insert/update trigger on MyTable2?

Poor planning on your part does not constitute an emergency on my part.

Yes it was update trigger error thanks for the tip

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources