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

Here is the query...so obviously the error makes no sense...

insert into reports
select * from [serverA.dbo.reports sva
where sva.reportnumber > (select top 1 reportnumber
from reports
order by reportnumber)


clearly there is only one result considering it actually specifies top 1.....
bccopsNetwork Operations ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UnifiedISCommented:
It makes sense, a where clause can't evaluate for each record

use a join to the reports instead of a subquery

insert into reports
select * from [serverA.dbo.reports sva
INNER JOIN (SELECT top 1 reportnumber from reports order by reportnumber) r
on sva.reportnumber > r.reportnumber

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ephraim WangoyaCommented:
you can do it the easy and clear way
declare @max int

select @max = MAX(reportnumber) from reports

insert into reports
select * 
from serverA.dbo.reports sva
where sva.reportnumber > @max

Open in new window

0
UnifiedISCommented:
The author's order by would be the MIN(reportnumber) not the max although MAX seems more likely to be the need.


You could also just put the aggregate in your where clause:

insert into reports
select * from [serverA.dbo.reports sva
where sva.reportnumber > (select MIN(reportnumber) from reports)
0
eridanixCommented:
Hi,

you can use one of the two queries bellow.
-- first method
INSERT INTO reports
SELECT TOP (1) * FROM serverA.dbo.reports sva
WHERE sva.reportnumber > (SELECT TOP 1 reportnumber FROM reports ORDER BY reportnumber)


-- second method
INSERT TOP (1) INTO reports
SELECT * FROM serverA.dbo.reports sva
WHERE sva.reportnumber > (SELECT TOP 1 reportnumber FROM reports ORDER BY reportnumber)

Open in new window

0
bccopsNetwork Operations ManagerAuthor Commented:
I know this is lame...but all of these work fine (and better than mine frankly) but the answer turned out to be that I had added an insert trigger. And for some reason this was cuasing the update to fail. Removal of the trigger and the query ran fine.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.