Re: sqlplus update table from table
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: barb.baker@xxxxxxxxx
- Date: Fri, 16 Apr 2010 20:22:20 +0200
Barbara,
A number of issues here:
1) You cannot reference variables at the inner level from the outer
level. It works like variable scoping in programs.
2) The fact that you have a remote table dictates a number of things.
For instance, you don't want an EXISTS nor correlation when you have a
database link between the outer and the inner table, because then you
have to execute a remote query for every row you inspect. Definitely not
what you want to do if you have a large number of rows to update. Might
take hours.
I dislike multi-pass operations, but this looks like a one-off update,
and I think that you'd be better off
1) Creating a temporary table by retrieving from the remote table key,
job_nbr and status for all the Apr 1st rows
2) Indexing this table on key and job_nr
3) Running a query with correlated subquery, but between two local
tables - while I was writing this, I have seen good solutions being
suggested.
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
Barbara Baker wrote:
> hi, all.
> oracle 9.2.0.7, linux
>
> I have identical tables in 2 databases (dwprod and repdb)
>
> I want to update column STATUS in table wof in database dwprod
>
> to be equal to STATUS in table wof in database repdb
>
> (using a database link)
>
> when the status in dwprod is ‘D’
>
> and the date and keys are equal and the date is april 1 2008
>
>
>
> I’ve tried a bunch of In’s Where’s Minuses, etc, but I’m not getting it.
>
>
>
> Any ideas?
>
> Thanks SO MUCH.
>
> Barb
>
>
>
> update ad_mart.wof a
>
> set a.status = b.status
>
> where (a.job_nbr,a.adj_nbr_key,a.issue)
>
> IN
>
> (select
>
> job_nbr,adj_nbr_key,issue
>
> from ad_mart.wof a ,
>
> ad_mart.wof@repdb b
>
> where
>
> ( a.job_nbr = b.job_nbr
>
> and a.adj_nbr_key = b.adj_nbr_key
>
> and a.issue = b.issue
>
> and a.issue = '01-APR-08'
>
> and a.status='D')
>
> )
>
> /
>
>
>
>
>
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
