UPDATE and DELETE inside a FETCH loop

From: puma <NOSPAMrupsd_at_tin.it>
Date: Fri, 1 Feb 2002 10:10:28 +0100
Message-ID: <a3dmjr$d0a34_at_intra09.infocamere.it>

Hi all,
I'ma facing this problem:
say TABLE_A has (COL1, COL2, COL3,COL4)
and TABLE_B has (COL1, COL2)
for each pair of COL1 and COL3 I have 2 records on table_B with COL2='0' and COL2='1'
my pseudocode is this

OPEN CURSOR 'SELECT COL1 FROM TABLE_A

                             WHERE COL4=:col4_bind
                                AND COL3= :col3_bind
                                AND COL2='1'

WHILE !NOTFOUND
            FETCH CURSOR     INTO :col1_bind

            DELETE FROM TABLE_B
            WHERE COL1=:col1_bind
                AND  COL2='0'

            UPDATE TABLE_B
                SET COL2='0'
                WHERE COL1=:col1_bind
                and COL2='1'

END WHILE CLOSE CURSOR   Can I do this? UPDATE statement return a record with the same key of record being deleted from DELETE statement and I COMMIT only at the end of while-loop. Are there problems?

Please give me an hint!
thanks Received on Fri Feb 01 2002 - 10:10:28 CET