Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Table Update Strategy

Re: Table Update Strategy

From: Christopher Latta <nobody_at_nowhere.not>
Date: Fri, 8 Jun 2001 11:16:01 +1000
Message-ID: <vKVT6.1248$qJ4.49871@ozemail.com.au>

A stored procedure (or just code in SQL Plus) like the following ought to do the trick (off the top of my head - may not be totally correct or the most efficient):

begin
  insert into A (select * from A_Update where PKField not in (select PKField from A));
  update A set
    Field1 = (select Field1 from A_Update where PKField = A.PKField),     Field2 = (select Field2 from A_Update where PKField = A.PKField),

Christopher Latta

Paul Nader <Paul.Nader_at_d22mail.alcatel.com.au> wrote in message news:3B200D59.5C1F10C9_at_d22mail.alcatel.com.au...
> I have two tables A and A_Update. Both tables are identical in stucture
 down to
> the primary key.
>
> The content of A_Update is replaced every so often by a run of the SQL
 Loader.
> These updates contain both rows already present in A (ie the primary keys
 match
> but the other columns may contain new values), and completely new rows (ie
> the primary key in A_Update is not present in A).
>
> What is the best strategy for updating A based on A_Update such that the
 common
> rows are updated, new rows are inserted and disjoint rows are left
unchanged? Received on Thu Jun 07 2001 - 20:16:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US