Register  |  Login



Question

Status: Closed Points: 75 Time: 17:24 - Oct 20, 2006  

caldcat

How to Update records of one oracle table from another table when there may be 1:N relationships.

I am trying to set a date in one table based on data in another.

The code I am using is as follows:
update &2 a
set a.ret_date =
 (select b.ret_date
 from &3 b
 where  a.pcode=b.pcode and
   a.pctype=b.pctype and
   a.lowaddr=b.lowaddr and
   a.highaddr=b.highaddr and
   a.std_stname=b.std_stname and
   a.std_sttype=b.std_sttype and
   a.std_stdir=b.std_stdir and
   a.std_comm_name=b.std_comm_name and
   (a.pctype like '1' or a.pctype like '2')
   );
There are cases where there is a one to one match between the tables based on the criteria,
but there are also cases where table &2 may have multiple records that match.

How can I update all the matching records in table &2, not just the first match?

Categories:

Answer Discussion
Answer Summaries

 

Q&A System for Websites and Corporate Collaboration

Advertisement

  • Generates significant organic traffic for websites
  • Saves companies money, resources, and time

nidhi

Date:: Apr 16, 2007

Time:: 06:51

I thought all matching records would of got updated and not just the first match. Oh well.
If you are having problems updating all records then have u considered writing this in PL/SQL and using cursors?

admin

Date:: Jun 04, 2007

Time:: 11:28

caldcat, please close this question and distribute the points.
If you didn't get a solution to your problem or you found it yourself, please leave a comment here to let the experts know.
Thanks,
Quomon Admin

admin

Date:: Jun 06, 2007

Time:: 07:01

The question has been closed.

Quomon Admin

Question Answered

This question has been closed, and points have been rewarded to the following experts:

nidhi: 75

You're welcome however to comment or give additional information or if you wish, you have the ability to write an Answer Summary for the Summary Area.

Answer this Question

New User

Email:

Upon submission of this form, you will automatically be registered as a Quomon user and we will send your login information to this address

Registered User

Username:

Password:

Forgot Your Password?

No summaries have been submitted yet. Want to be the first?

Answer this Question

New User

Email:

Upon submission of this form, you will automatically be registered as a Quomon user and we will send your login information to this address

Registered User

Username:

Password:

Forgot Your Password?

Ask a Question

Have a new question? Ask!

You have 100 characters to use



Top Tables Experts

View More

Rank

Expert

Points

1.

rcastagna

165

2.

jgivoni

125

3.

nidhi

75

4.

James1

15

Become an Expert

Register today to share your knowledge with the community and be recognized and rewarded for your contributions.


Register Here




"Psst, Quomon is a great site. Pass it on."     Tell a Friend  |   Link To Us  |   Save to Delicious  |   Digg! Digg it



Language Options

English:

www.quomon.com

Español:

www.quomon.es