Hi,

> I've got a situation similar to the "subselect in update" thread, but
> I'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

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81