> -----Original Message-----
> From: Janet Linsy [mailto:[EMAIL PROTECTED]]
>
> I got
>
> (select c.franchise_name
> *
> ERROR at line 3:
> ORA-01427: single-row subquery returns more than one
> row
>
> How can I solve this? Thank you again!
>
> SQL> update service_location a
> set a.central_of 2 fice_code =
> (selec 3 t c.franchise_name
> from 4 service_location a,
> 5 service_loc_area b,
> 6 franchise_area c
> wh 7 ere a.service_location_id =
> b.service_location_id
> 8 and c.franchise_id = b.franchise_id) ;
a) It's a better idea to send your questions back to the list, so that you get more answers.
b) I think you made a mistake in your update statement. My example was:
update service_location a
set a.central_office_code =
(select c.franchise_name
from service_loc_area b,
franchise_area c
where a.service_location_id = b.service_location_id
and c.franchise_id = b.franchise_id) ;
Whereas you had an extra join to service_location in the sub-select, to wit
> SQL> update service_location a
> set a.central_office_code =
> (select c.franchise_name
> from service_location a, <--------------------
> service_loc_area b,
> franchise_area c
> where a.service_location_id = b.service_location_id
> and c.franchise_id = b.franchise_id) ;
c) If you still get the same error, you must ask yourself the following question: for a particular service_location_id, are you sure that there can only be one row returned from the sub-select? Look at the results of this query:
select a.service_location_id, count (*)
from service_location a, service_loc_area b, franchise_area c
where a.service_location_id = b.service_location_id
and b.franchise_id = c.franchise_id
group by a.service_location_id
having count (*) > 1 ;
If the query returns some rows, you will have to figure out which of the multiple possible franchise_id to use for a service_location_id.
You can use this query to find the service_location_ids that have more than one franchise_id:
select distinct a.service_location_id, c.franchise_id
from service_location a, service_loc_area b, franchise_area c
where a.service_location_id = b.service_location_id
and b.franchise_id = c.franchise_id
and a.service_location_id in
(select d.service_location_id
from service_location d, service_loc_area e, franchise_area f
where d.service_location_id = e.service_location_id
and e.franchise_id = c.franchise_id
group by d.service_location_id
having count (*) > 1) ;
If there is only one distinct franchise_id in franchise_area for each service_location_id in service_location, you could use any group function that will reduce the number of rows returned to one, like a min or max function, e.g.
update service_location a
set a.central_office_code =
(select min (c.franchise_name)
from service_loc_area b,
franchise_area c
where a.service_location_id = b.service_location_id
and c.franchise_id = b.franchise_id) ;
