Re: new to pl/sql, cant find an example, trying to write a script
From: Wanderley <wces123_at_yahoo.com>
Date: Sun, 05 Jan 2003 05:34:47 GMT
Message-ID: <XtPR9.108631$6H6.3610590_at_twister.austin.rr.com>
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 - 06:34:47 CET
Date: Sun, 05 Jan 2003 05:34:47 GMT
Message-ID: <XtPR9.108631$6H6.3610590_at_twister.austin.rr.com>
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 - 06:34:47 CET