Subject SQL problem
Author John Costanzo
Hello, I realize this is not an IBO problem but thought with all the
SQL people in the group it mught be a good place to ask this question.

I have the following situation. I need to loop through a table,
select matching rows from another table based on a column in the 1st
table, then update some colukmns in the 1st table based on
calculations. This is what I cam up with:

CREATE PROCEDURE TEST
AS
DECLARE VARIABLE COLAVAR SMALLINT;
DECLARE VARIABLE COLBVAR NUMERIC(18,2);
DECLARE VARIABLE COLCVAR NUMERIC(18,2);
BEGIN
FOR SELECT COLA
FROM TABLE1
INTO COLAVAR
DO
BEGIN
SELECT SUM(COLB),Sum(COLC)
FROM TABLE2
WHERE (COLA = :COLAVAR)
INTO :COLBVAR,:COLCVAR;
UPDATE TABLE1
SET COLX = :COLBVAR - COLCVAR
WHERE COLA = :COLAVAR;
END
END #

The FOR SELECT works fine. The SELECT SUM works fine. The problem I
have is with the UPDATE. It seems to me that since I am already
in a FOR SELECT loop there should be a way to update the currently
selected row without the need to reselect. The documentation shows a
statement WHERE CURRENT OF cursor. This seems what I need but It
gives me an error. Is there another way or is the way I am doing it
correct. I am using Delphi7, Firebird, and IBO. Thanks in advance.
Geno