Thanks, Arno. It still doesn't quite work - I'm getting "Multiple rows
in singleton select" - and I suspect it's because, while PERMIT_NUMBER
is the primary key of PERMITS, there's no way for Firebird to know that
it's also unique in V_SUM_FEES. I may have to resort to a stored
procedure after all.

Thanks again,

Steve

> Message: 3
> Date: Fri, 13 Aug 2004 00:41:01 +0200
> From: "Arno Brinkman" <firebird@...>
> Subject: Re: Specific subselect in update: similar to "subselect in

update : how to force a plan ?"

>
>>Hi,
>> I've got a situation similar to the "subselect in update" thread, but
>>
>>d like to insert a different value per record. V_SUM_FEES.SUM_FEE has
>> the number I want to store in PERMITS.FEE_CALC. They are related by
>> field PERMIT_NUMBER (V_SUM_FEES is a view based on PERMITS).
>>
>> So instead of:
>>
>> UPDATE PERMITS
>> SET PERMITS.FEE_CALC = 1
>> WHERE EXISTS
>> (SELECT V_SUM_FEES.SUM_FEE
>> FROM PERMITS INNER JOIN V_SUM_FEES ON PERMITS.PERMIT_NUMBER =
>> V_SUM_FEES.PERMIT_NUMBER);
>>
>> I need something like this:
>>
>> UPDATE PERMITS
>> SET PERMITS.FEE_CALC = V_SUM_FEES.SUM_FEE
>> WHERE PERMIT_NUMBER IN
>> (SELECT SUM_FEE, PERMIT_NUMBER FROM V_SUM_FEES);
>
>
> If permit_number is unique then you can do it with this :
>
> UPDATE
> PERMITS
> SET
> PERMITS.FEE_CALC = (SELECT V_SUM_FEES.SUM_FEE FROM V_SUM_FEES
> WHERE V_SUM_FEES.PERMIT_NUMBER = PERMIT_NUMBER)
> WHERE
> PERMIT_NUMBER IN
> (SELECT V_SUM_FEES.PERMIT_NUMBER FROM V_SUM_FEES);
>
> For speed you could probably better write a Stored Procedure.
> Which walks through V_SUM_FEES and updates PERMITS
>
> Regards,
> Arno Brinkman
> ABVisie