Sub query message

[S G]

Hi all,
I have a query as follows:

update stage.FinanceVe hicleSummary
set vin = (select VIN from stage.ChasisLoo kup where
stage.chasisloo kup.chasis = stage.FinanceVe hicleSummary.Ch asisNo
and stage.chasisloo kup.sr_registra tion_number =
stage.FinanceVe hicleSummary.sr regno)

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]

Re: Sub query message

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 FinanceVehicleS ummary
SET vin = (SELECT MAX(chasislooku p.VIN)
FROM ChasisLookup
WHERE chasislookup.ch asis = FinanceVehicleS ummary.ChasisNo
AND chasislookup.sr _number = FinanceVehicleS ummary.srregno)
WHERE ... ;

--
- Anith
( Please reply to newsgroups only )

[Ronnie Chee]

Re: Sub query message

"S G" <sgpgpjr@yahoo. ie> wrote in message
news:3f293b67$0 $200$75868355@n ews.frii.net...[color=blue]
>
> Hi all,
> I have a query as follows:
>
> update stage.FinanceVe hicleSummary
> set vin = (select VIN from stage.ChasisLoo kup where
> stage.chasisloo kup.chasis = stage.FinanceVe hicleSummary.Ch asisNo
> and stage.chasisloo kup.sr_registra tion_number =
> stage.FinanceVe hicleSummary.sr regno)
>
> 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[/color]

This part of the query is returning more than one VIN

select VIN from stage.ChasisLoo kup where
stage.chasisloo kup.chasis = stage.FinanceVe hicleSummary.Ch asisNo
and stage.chasisloo kup.sr_registra tion_number =
stage.FinanceVe hicleSummary.sr regno

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