Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL Update/Insert, Need Help
Hi,
Step 1
Create Index Master file and Index file.
CREATE INDEX master_index on master_table (LC,ITEM) CREATE INDEX input_index on input_table (LC,ITEM) (This index optional)
Step 2
Write an PL/SQL
DECLARE
EXCEPTION master_found;
W_LC varchar2(3); W_ITEM varchar2(4); M_LC varchar2(3); M_ITEM varchar2(4); W_ORDER varchar2(1); W_CUST varchar2(30) -- give the exact lengthCURSOR input_cursor is select LC,ITEM,ORDER,CUST FROM input_file; BEGIN
END;
END LOOP;
END;
Hope this helps,
Regards,
MG
Tim Lindsey <timlind_at_hotmail.com> wrote in message
news:Fnu2u9.7rr_at_news.boeing.com...
> I have a large master file I want to update with new data. The key
> fields are LC and ITEM.
>
> If there is a matching record in the new input file I want to update
the
> ORDER and CUST fields.
>
> If there is no match I want to insert the new record.
>
> I assume I need to have both files indexed by the key fields. Does
> anybody know how I can do this? I am an experienced programmer but I'm new
> at writing PL/SQL.
>
> Master File
> SQLWKS> select * from ta_a;
> LC ITEM ORDER CUST
> --- --------------------------------
> 605 5555 A LIGHT STRUCTURES
> 602 2222 A MACHINING
> 603 3333 A ASSEMBLY
>
> New Input
> SQLWKS> select * from ta_b;
> LC ITEM ORDER CUST
> --- --------------------------------
> 602 2222 B FABRICATION
> 604 4444 A SHIPPING
>
> Updated Master File
> SQLWKS> select * from ta_a;
> LC ITEM ORDER CUST
> --- --------------------------------
> 605 5555 A LIGHT STRUCTURES
> 602 2222 B FABRICATION (record modified)
> 603 3333 A ASSEMBLY
> 604 4444 A SHIPPING (record added)
>
>
>
>
Received on Tue Jan 04 2000 - 19:02:57 CST
![]() |
![]() |