Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Table Update Strategy
Make that
update ...
set (fielda, fieldb) = (select fielda, fieldb from .... ) etc
Hth,
Sybrand Bakker, Oracle DBA
"Christopher Latta" <nobody_at_nowhere.not> wrote in message
news:vKVT6.1248$qJ4.49871_at_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),
> -- etc, etc - don't need to update PKField as it will be the same
> where PKField in (select PKField from A_Update);
> end;
>
> 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 Fri Jun 08 2001 - 13:20:25 CDT