Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-10-05
5
Medium Priority
?
439 Views
Last Modified: 2012-05-12
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.....
0
Comment
Question by:bccops
5 Comments
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 1000 total points
ID: 36920564
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
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 500 total points
ID: 36921626
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
 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 1000 total points
ID: 36921793
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
 
LVL 5

Assisted Solution

by:eridanix
eridanix earned 500 total points
ID: 36929698
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
 

Author Closing Comment

by:bccops
ID: 36943969
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question