At 06:07 PM 31/01/2003 +1300, you wrote:

>Hi all,
>
>I have an update query which updates a table (currently only with 9,500
>records) based on the results of another query.

Just be very careful to use fully-qualified fieldnames in any multi-table
operations (whether DSQL or precompiled as views or SPs) to avoid getting
wrong results which may not be evident. If you don't provide it, the fact
that a query runs fast (or runs successfully) doesn't mean, per se, that
you have achieved the intended result.

Here is your original query, corrected so that you get the desired
correlation between the invoice being updated and the one being queried:

Update jb_Invoice i
Set i.Amount = (
select
SUM(il.Quantity * ili.Invoice_Price)
FROM
jb_Invoice_Line il
JOIN jb_Invoice_Line_Item ili
ON
il.LINE_NR = ili.LINE_NR AND il.INVOICE_NR = ili.INVOICE_NR
WHERE
il.Invoice_Nr = i.Invoice_Nr

Try this to see whether it is faster. However, I would consider replacing
it with a stored procedure similar to your example - looping through the
providing set is the best way to do this update. A subquery is pretty
certain to be slower for this.

Now, here is your SP code, corrected to resolve ambiguities:
For
select
il.Invoice_Nr,
SUM(il.QUANTITY * ili.Invoice_Price)
FROM
JB_INVOICE_LINE il
JOIN JB_INVOICE_LINE_ITEM ili
ON il.LINE_NR = ili.LINE_NR) AND il.INVOICE_NR = ili.INVOICE_NR
Group BY il.Invoice_Nr
Into
:InvoiceNr,
:Amount
do begin
Update Ct_Debtor_Transaction
set Ct_Debtor_Transaction.Amount = :Amount
Where Ct_Debtor_Transaction.Invoice_Nr = :InvoiceNr;
end

To save a lot of typing and clutter, it's worth knowing that Fb/IB SQL does
not require the INNER keyword. JOIN on its own is always an INNER
JOIN. Likewise, the OUTER keyword is also not needed. LEFT JOIN, RIGHT
JOIN and FULL JOIN imply outer joins in Fb/IB.

>The structure is basically an invoice table with an invoice_line table
>and an invoice_line_item table. We are bringing the amount of the
>invoice up into the invoice table to speed other queries.

I don't like this kind of redundancy. Have you utterly proven to yourself
that it will speed other queries sufficiently to justify the synchronicity
problems you are giving your system by storing your detail values as a hard
aggregate?

regards,
heLen