You're right. Thanks for correction. On Fri, Apr 16, 2010 at 2:25 PM, Jacques Kilchoer < Jacques.Kilchoer@xxxxxxxxx> wrote: > You might want to add to you where clause > > > > AND EXISTS > > (select null from ad_mart.wof@repdb b where b.job_nbr = a.job_nbr and > b.adj_nbr_key = a.adj_nbr_key) > > > > because without the AND EXISTS clause you will be updating rows in A that > have no corresponding vlaue in B. > > > > *De :* oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] > *De la part de* Igor Neyman > *Envoyé :* vendredi, 16. avril 2010 11:08 > *À :* barb.baker@xxxxxxxxx > *Cc :* ORACLE-L > *Objet :* Re: sqlplus update table from table > > > > Barbara, > > Try this: > > UPDATE ad_mart.wof a > SET a.status = ( > SELECT b.status > FROM ad_mart.wof@repdb b > WHERE b.job_nbr = a.job_nbr > AND b.adj_nbr_key = a.adj_nbr_key) > WHERE a.issue = b.issue > and a.issue = '01-APR-08' > and a.status='D' > / > > Igor Neyman > > On Fri, Apr 16, 2010 at 1:46 PM, Barbara Baker <barb.baker@xxxxxxxxx> > 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') > > ) > > / > > > > > > > > > > >