Re: sqlplus update table from table

Barb,

It usually helps me to write SQL in more than one pass. In other words,
write parts that make sense, then combine or expand the parts to make the
whole.

Let me illustrate:

Step 1- I want to update column STATUS in table wof in database dwprod when
the status in dwprod is ‘D’ and the date is april 1 2008

update ad_mart.wof a

set a.status = ?

where a.status='D'

and a.issue = '01-APR-08'


Step 2- I want status to be equal to STATUS in table wof in database
repdb (using a database link) where the date and keys are equal and the date
is april 1 2008


select b.status from 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


Step 3 - combine

update ad_mart.wof a

set a.status = (

  select b.status from 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

)

where a.status = 'D'

  and a.issue  = '01-APR-08'
/

Ron

Other related posts: