471,073 Members | 1,402 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,073 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 12222
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Wm | last post: by
9 posts views Thread by netpurpose | last post: by
6 posts views Thread by paii | last post: by
7 posts views Thread by Simon Bailey | last post: by
8 posts views Thread by Adam Louis | last post: by
5 posts views Thread by Daniel Tan | last post: by
10 posts views Thread by Randy Harris | last post: by
3 posts views Thread by Kevin M | last post: by
6 posts views Thread by Phil Stanton | last post: by

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.