[quoted text, click to view]

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

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 ***

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 )