| passing values [message #38929] |
Tue, 28 May 2002 13:10  |
Toyn
Messages: 36
Registered: April 2001
|
Member |
|
|
Is there a way to gather a value, then insert/update that value to another table using a join?
update orders_test set (patient_id) =
(select distinct(a.phs_internal) from exams_mrns_temp a, bics_temp_orders b
where a.bwh_mrn_cleaned(+)=b.mrn);
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
So far, with my select statement, I'm able to retrieve the data that I want. The update fails because of the error listed. I want to insert the phs_internal into orders_test.patient_id where a.bwh_mrn_cleaned=b.mrn. However, missing from my join is the fact that I also want this number inserted where orders_test.order_num=bics_temp_orders.order_num. How can I incorporate this third table into my join logic? Thanks.
|
|
|
|
| Re: passing values [message #38934 is a reply to message #38929] |
Tue, 28 May 2002 22:07  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
update orders_test t
set patient_id =
(select distinct(a.phs_internal)
from exams_mrns_temp a, bics_temp_orders b
where b.order_num = t.order_num
and a.bwh_mrn_cleaned(+) = b.mrn);
Now that this is a correlated subquery, hopefully the subquery will return a maximum of one row for each row in orders_test - otherwise you will still see the ORA-01427 error.
|
|
|
|
| Re: passing values [message #39777 is a reply to message #38929] |
Fri, 09 August 2002 07:57 |
Dave
Messages: 92
Registered: August 1999
|
Member |
|
|
Todd,
Are you working in Access with this?
I have a field in a table (tbl_Patients) called Patient #. In my form I want to pass the Patient # value to a text box which will save to a different table (tbl_atty) to a field with the same name (Patient #)
Any thoughts or ideas? Thank you
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
