RE: sqlplus update table from table
- From: Jacques Kilchoer <Jacques.Kilchoer@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 16 Apr 2010 11:25:08 -0700
You might want to add to you where clause
AND EXISTS
(select null from ad_mart.wof@repdb<mailto: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<mailto: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')
)
/
Other related posts:
