Re: sqlplus update table from table

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')
>
> )
>
> /
>
>  
>
>  
>
>  
>
>


--
http://www.freelists.org/webpage/oracle-l


Other related posts: