Updating Records With a Cursor 1

[lynchg]

I have a situation in which I am initially inserting a set of records into a table (tableA) and then, using a series of updates I need to update a field (tableA.field1) in the same table which was not populated during the initial insert. I am plannining on using the For Update Of and Where Current Of clauses in a cursor in order to update these fields.

My problem is that the fields I am updating are being populated based on individual subselects of data from another table (tableB), and the data to populate tableA.field1 could be in either of 2 records in tableB. I want to try for the data (tableB.field2) from one record in tableB based on a criterion in another field in tableB (tableB.field3). If there is data in tableB.field2 for that record, that is great, I update tableA.field1 with that data, if tableB.field2 is empty, I want to requery tableB using a different value for my tableB.field3 criterion.

Example (I want to duplicate this logic and I am wondering if I can do it all inside of my cursor):

set tableA.field1 = (Select tableB.field2 from tableB where tableB.field3 ='abc')

If tableA.field1 is null Then
Set tableA.field1 = (Select tableB.field2 from tableB where tableB.field3 ='xyz')

I would like to be able to do this while I am sitting on an individual record in my update cursor, but I don't know if this is possible.

Any help would be appreciated.

 

[SantaMufasa]

Lynch,

First, let's clarify that at this point in history, PL/SQL cursors are only for sequential (not random) processing of a specific set of rows which you SELECT. You cannot use a user-defined cursor for INSERTs or UPDATEs.

Second, I recommend that you use this update logic/code in your PL/SQL block:

Select field2 into field2_hold from tableB where field3 ='abc';
IF field2_hold is null then
    Select field2 from tableB where field3 ='xyz';
end if;
Update tableA set field1 = field2_hold where current of <cursor_name>;

Does this sound reasonable and seem to resolve your need?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 20:34 (15Feb05) UTC (aka "GMT" and "Zulu"),
@ 13:34 (15Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[lynchg]

That looks like it will work great, thanks.