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!

[Anith Sen]

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 )

[Ronnie Chee]

"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