Register  |  Login




Advertisement

Start Your Own Q&A Site

Create your own Q&A site easily, allowing you to quickly grow a new community around any subject matter or generate new organic traffic for your existing website.

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
Tutorials

 

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 a Tutorial in the Tutorial 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 tutorials 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 Experts

View More

Rank

Expert

Points

1.

nidhi

10354

2.

oracleofDelphi

6493

3.

rcastagna

5596

4.

LAGM

4848

5.

PeterNZ

3487

6.

gonzalo

2840

7.

Mason

2770

8.

jgivoni

2303

9.

xarcus

1820

10.

Anpanman

917

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