Register  |  Login

Question Details    

   Question

Time: 17:24 - Oct 20, 2006     Asked by: caldcat      Status: Answered      Points: 75   

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?

Ask a New Question

Become a Quomon Expert

Current Categories

 

Other Questions Needing Answers


   

Answer Discussion
Answer Discussion
Answer Summaries
Answer Summary
 
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?

Expert:

nidhi

Date:

Apr 16, 2007

Time:

06:51

 

Votes: Good (0) | Bad (0)
Login to rate this answer

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

Expert:

admin

Date:

Jun 04, 2007

Time:

11:28

 

Votes: Good (0) | Bad (0)
Login to rate this answer

The question has been closed.

Quomon Admin

Expert:

admin

Date:

Jun 06, 2007

Time:

07:01

 

Votes: Good (0) | Bad (0)
Login to rate this answer

Question Answered

This question has been answered, 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 this question by clicking on the "Answer Summaries" Tab.

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



Respond to 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:


Forgotten Password

 

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:


Forgotten Password

   

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


All Questions


Language Options

English:

www.quomon.com

Español:

www.quomon.es

Quomon Blog

blog.quomon.com

Sponsors

Questions and Answers Software
Real Estate Postcards
Marketing Fulfillment