473,371 Members | 1,508 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,371 software developers and data experts.

Sub query message

S G

Hi all,
I have a query as follows:

update stage.FinanceVehicleSummary
set vin = (select VIN from stage.ChasisLookup where
stage.chasislookup.chasis = stage.FinanceVehicleSummary.ChasisNo
and stage.chasislookup.sr_registration_number =
stage.FinanceVehicleSummary.srregno)

and i get this message: Subquery returned more than 1 value. This is not
permitted when the subquery follows =, !=, <, <= , >, >= or when the
subquery is used as an expression.
The statement has been terminated.

and I have no idea what could be causing this or how to resolve it?
Many thanks
Sam

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #1
2 12280
The parenthesized subquery in the SET clause of an UPDATE statement must be
scalar. Since you are using correlation based on columns with values which
are not unique, your sub-SELECT returned multiple values and this causes the
error.

Unless you provide the detailed DDL & sample data, a correct solution cannot
be suggested. However, to avoid the error, you can use a aggregate function
like MIN() or MAX() on your column in the sub-SELECT list like:

UDPATE FinanceVehicleSummary
SET vin = (SELECT MAX(chasislookup.VIN)
FROM ChasisLookup
WHERE chasislookup.chasis = FinanceVehicleSummary.ChasisNo
AND chasislookup.sr_number = FinanceVehicleSummary.srregno)
WHERE ... ;

--
- Anith
( Please reply to newsgroups only )
Jul 20 '05 #2
"S G" <sg*****@yahoo.ie> wrote in message
news:3f*********************@news.frii.net...

Hi all,
I have a query as follows:

update stage.FinanceVehicleSummary
set vin = (select VIN from stage.ChasisLookup where
stage.chasislookup.chasis = stage.FinanceVehicleSummary.ChasisNo
and stage.chasislookup.sr_registration_number =
stage.FinanceVehicleSummary.srregno)

and i get this message: Subquery returned more than 1 value. This is not
permitted when the subquery follows =, !=, <, <= , >, >= or when the
subquery is used as an expression.
The statement has been terminated.

and I have no idea what could be causing this or how to resolve it?
Many thanks
Sam


This part of the query is returning more than one VIN

select VIN from stage.ChasisLookup where
stage.chasislookup.chasis = stage.FinanceVehicleSummary.ChasisNo
and stage.chasislookup.sr_registration_number =
stage.FinanceVehicleSummary.srregno

Without DDL and data for the tables it is difficult to tell why this is
happening - you could add more columns to the above query to diagnose.
Possibly you need some more conditions so that it only returns one VIN.

HTH
Ronnie
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Wm | last post by:
I have a query that I expect to return 3 or 4 entries -- but I seem to be getting only the most recent entry, repeated 4 times. What am I doing wrong here? $query="SELECT...
9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
6
by: paii | last post by:
I have a table that stores job milestone dates. The 2 milestones I am interested in are "Ship Date" TypeID 1 and "Revised Ship Date" TypeID 18. All jobs have TypeID 1 only some jobs have TypeID 18....
7
by: Simon Bailey | last post by:
How do you created a query in VB? I have a button on a form that signifies a certain computer in a computer suite. On clicking on this button i would like to create a query searching for all...
8
by: Adam Louis | last post by:
I would like help resolving this problem. I'm a novice who's been hired to query a hospital database and extract useful information, available to me only in a dynamically generated, downloadable...
5
by: Daniel Tan | last post by:
Are there anyway to copy rows of records from one query to another query and then hide the records in source query ? Pls advise. Thanks. Regards, Daniel
10
by: Randy Harris | last post by:
I imported records into a table, later found out that many of them had trailing spaces in one of the fields. If I'd caught it sooner, I could have trimmed the spaces before the import. This...
15
by: Richard Hollenbeck | last post by:
I tried to ask this question before on the 14th of January but I never got a reply. I'm still struggling with the problem. I'll try to rephrase the question: I have a crosstab query with rows...
3
by: Kevin M | last post by:
I have one table and have created a form from that table. Also, I have created a delete query. I want to but a button on the form which will delete all records from the table; however, I cannot get...
6
by: Phil Stanton | last post by:
I am running a query that calls a function used to format addresses depending on the width of a control on a report that shows that address. The same query is used as the RecordSource of lots of...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.