Re: new to pl/sql, cant find an example, trying to write a script

Hey Wanderley thanks for responding.

I thought I included everything in my first post, I left out that if the data from table B is not in table A I need to increment the seqno before inserting.

Table A and B are work tables that I've created to update a table in the system.

The data that might be on table A is benefit data. Employees are in table A and their dependents are in table B. For every dependent in table B I need to add to or update table A and increment the seqno.

For example. table A has

123456789 01 - employee

table B has

123456789 - same ssn as employee but this might be a child or spouse

I believe its Oracle 8i.

Thanks again for the info.

On Sun, 05 Jan 2003 05:34:47 GMT, Wanderley <wces123_at_yahoo.com> wrote:

>Please send descriptions of tables A and B (mainly primary keys) and
>Oracle version. I believe you're just trying to implement the UPSERT
>concept (update otherwise insert). Which, by the way, is available for
>Oracle9i through the MERGE statement
>(http://otn.oracle.com/products/oracle9i/daily/Aug24.html).
>
>Otherwise, this code may help you (assuming SEQNO is the primary key):
>
>FOR B_ IN (SELECT * FROM B) LOOP
> BEGIN
> INSERT INTO A (SEQNO,SSN)
> VALUES (B_.SEQNO, B_.SSN);
> EXCEPTION
> WHEN DUP_VAL_ON_INDEX THEN
> UPDATE A
> SET A.SSN = B_.SSN
> WHERE A.SEQNO = B_.SEQNO;
> END;
>END LOOP;
>
>
>whonose_at_nowhere.com wrote:
>> i have ssn and seqno in table A, i'm trying to either insert from
>> table B into table A or update table A based on what's in table B
>>
>> if i create a cursor to process table B, i'm not sure how to find if
>> the data from table B is in table A
>>
>> i know i'm reading data from table A because i started coding with
>> just trying to read through it
>>
>> after the notfound for reading through the table A cursor, i tried to
>> read for the data in table B but i'm not getting a match, it's like
>> all the data in table is not found in table B
>>
>> i displayed the data from variables from table A, but it's like the
>> sql to read table B is not working
>>
>> i know this is a bit sketchy, i can provide more detail if anyone
>> wants to help
>>
>> tia
Received on Sun Jan 05 2003 - 17:22:54 CET