RE: update query??? HELP!!!
Title: RE: update query??? HELP!!! -Original Message- From: Janet Linsy [mailto:[EMAIL PROTECTED]] How to do this query, I have three tables: SERVICE_LOCATION a, SERVICE_LOC_AREA b, FRANCHISE_AREA c. The relationship between them is: a.SERVICE_LOCATION_ID = b.SERVICE_LOCATION_ID and b.FRANCHISE_ID = c.FRANCHISE_ID I need to update CENTRAL_OFFICE_CODE column in table SERVICE_LOCATION a, using a value selected from FRANCHISE_AREA c. a and c are related through b. update SERVICE_LOCATION set CENTRAL_OFFICE_CODE = (select FRANCHISE_NAME from FRANCHISE_AREA) where a.SERVICE_LOCATION_ID = b.SERVICE_LOCATION_ID and b.FRANCHISE_ID = c.FRANCHISE_ID) I don't know where to specify a, b, c tables. I believe this is the statement you want (assuming that onlye one franchise_name will be returned for a particular service_location_id) 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) ;
Re: update query??? HELP!!!
update SERVICE_LOCATION a set CENTRAL_OFFICE_CODE = (select FRANCHISE_NAME from FRANCHISE_AREA c, SERVICE_LOC b where a.SERVICE_LOCATION_ID = b.SERVICE_LOCATION_ID and b.FRANCHISE_ID = c.FRANCHISE_ID) At 02:55 PM 11/14/01 -0800, you wrote: Hi all, How to do this query, I have three tables: SERVICE_LOCATION a, SERVICE_LOC_AREA b, FRANCHISE_AREA c. The relationship between them is: a.SERVICE_LOCATION_ID = b.SERVICE_LOCATION_ID and b.FRANCHISE_ID = c.FRANCHISE_ID I need to update CENTRAL_OFFICE_CODE column in table SERVICE_LOCATION a, using a value selected from FRANCHISE_AREA c. a and c are related through b. update SERVICE_LOCATION set CENTRAL_OFFICE_CODE = (select FRANCHISE_NAME from FRANCHISE_AREA) where a.SERVICE_LOCATION_ID = b.SERVICE_LOCATION_ID and b.FRANCHISE_ID = c.FRANCHISE_ID) I don't know where to specify a, b, c tables. Thank you! Janet __ Do You Yahoo!? Find the one for you at Yahoo! Personals http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: update query??? HELP!!!
Title: RE: update query??? HELP!!! -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) ;
