RE: update query??? HELP!!!

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 14 Nov 2001 17:16:09 -0800
Message-ID: <F001.003C54E2.20011114170109@fatcity.com>

> -----Original Message-----
> From: Janet Linsy [mailto:janetlinsy_at_yahoo.com]
>
> 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) ; 

1. It's a better idea to send your questions back to the list, so that you get more answers.
2. 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) ;

Received on Wed Nov 14 2001 - 19:16:09 CST