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?
Status:
Open Oct 20, 2006 - 02:24 PM
Tables, Databases, oracle, sql
1answer
Answer
Apr 16, 2007 - 03:51 AM
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?
Answer this question
Share Your Own Experience & Expertise
We look to ensure that every question is answered by the best people with relevant expertise and experience, the best answers include multiple perspectives. Do you have relevant expertise or experience to contribute your answer to any of these commonly asked questions?
Add New Comment