Subject | Specific subselect in update: similar to "subselect in update : how to force a plan ?" |
---|---|

Author | Steve Staneff |

Post date | 2004-08-12T22:33:48Z |

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);

or this:

UPDATE PERMITS

SET PERMITS.FEE_CALC = V_SUM_FEES.SUM_FEE

FROM V_SUM_FEES

WHERE PERMITS.PERMIT_NUMBER = V_SUM_FEES.PERMIT_NUMBER;

but I can't get either to work in FB1.5. Any suggestions will be

appreciated.

Thanks in advance,

Steve

--------------------------

Construction Data and Research, Inc.

reply to: staneff@... <or>

staneff@...

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);

or this:

UPDATE PERMITS

SET PERMITS.FEE_CALC = V_SUM_FEES.SUM_FEE

FROM V_SUM_FEES

WHERE PERMITS.PERMIT_NUMBER = V_SUM_FEES.PERMIT_NUMBER;

but I can't get either to work in FB1.5. Any suggestions will be

appreciated.

Thanks in advance,

Steve

--------------------------

Construction Data and Research, Inc.

reply to: staneff@... <or>

staneff@...