Re: cursor

From: "Steve Wood" >>
I need to update the assets table with the first recline entry for each asset. There may be several entries in the recline table for each entry in the asset table but I only need the first one for each asset. <<

The first two tables are a parent child relationship and your problem is how to update a third table for the first child row for each parent-key, child-key set. This does not seem to be very difficult, but since Oracle stores the child rows in random order in the table based on freespace availability and insert patterns (all at once or one child row today and another tomorrow) this brings up the question of which child row is the first row?

Is any one child row good enough or do you need the oldest child row? If you need the oldest child then you need to add a sequence column to the child table so you can find it unless a receipt date column serves this purpose.

I would use PL/SQL and a cursor to select all rows from the 'reclines' table and then fetch only the first row. I would guess that you would need to select the header in a driving cursor and place the reclines cursor inside a loop so it can be executed once for each row returned from header.

I hope this is a possible solution for you.   

Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Fri Jul 10 1998 - 19:50:46 CDT