Solved

PL/SQL update with joining other tables

Posted on 2010-08-18
2
357 Views
Last Modified: 2013-12-19
I am trying to do a simple update statement in PL/SQL, Oracle 9i.

Table A
col1    col2     col3
1         1          Y
2         1          N
3         1          

Table B
col1    col2    col3
1         1         Y
2         1         Y
3         1         Y

All I need to do is the update Table B col3  the same as in Table A col3, if Table A col3 is null, then update as N.

I know oracle doesn't have the update from like SQL Server.  This makes it very difficult for me.  Please help.  Thanks.
0
Comment
Question by:cmleung2
2 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33470278
update
 (select b.col1, b.col2, a.col3 as col3a, b.col3 as col3b
  from tableb b left join tablea a on a.col1=b.col1 and a.col2=b.col2) t
set
 t.col3b=nvl(t.col3a, 'N')
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 33470313
update tableB b
set col3 = nvl((select col3 from tableA a where a.col1=b.col1 and a.col2=b.col2),'N')
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Join Greg Farro and Ethan Banks from Packet Pushers (http://packetpushers.net/podcast/podcasts/pq-show-93-smart-network-monitoring-paessler-sponsored/) and Greg Ross from Paessler (https://www.paessler.com/prtg) for a discussion about smart network …
If you're not part of the solution, you're part of the problem.   Tips on how to secure IoT devices, even the dumbest ones, so they can't be used as part of a DDoS botnet.  Use PRTG Network Monitor as one of the building blocks, to detect unusual…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now