RE: Update query
SELECT a.sql_basics ,b.chapter3 FROM bookshelf ,b.book WHERE b.chapter3 = 'basic sql constructs' AND a.sql_basics = 'book that should be read before posting requests like this'; ;o) -Original Message- Sent: 05 December 2001 09:55 To: Multiple recipients of list ORACLE-L Hallo, How can I update the field PRICE in table B with prices from field NEW_PRICE in table A. with condition LANGUAGE_CODE IS = 412 (that condition should be taken from table B) Please give me an example of a sql statementö. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Thomas, Kevin 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
And yea, I got the syntax wrong ;o)) Don't point it out to methe bad day continues... -Original Message- Sent: 05 December 2001 11:25 To: '[EMAIL PROTECTED]' Importance: High SELECT a.sql_basics ,b.chapter3 FROM bookshelf ,b.book WHERE b.chapter3 = 'basic sql constructs' AND a.sql_basics = 'book that should be read before posting requests like this'; ;o) -Original Message- Sent: 05 December 2001 09:55 To: Multiple recipients of list ORACLE-L Hallo, How can I update the field PRICE in table B with prices from field NEW_PRICE in table A. with condition LANGUAGE_CODE IS = 412 (that condition should be taken from table B) Please give me an example of a sql statementö. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Thomas, Kevin 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
Roland, How about update B set PRICE = (select NEW_PRICE from A where join condition betn a and b) where language_code = 412 Shailesh -Original Message- Sent: Wednesday, December 05, 2001 6:35 AM To: Multiple recipients of list ORACLE-L Hallo, How can I update the field PRICE in table B with prices from field NEW_PRICE in table A. with condition LANGUAGE_CODE IS = 412 (that condition should be taken from table B) Please give me an example of a sql statementö. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Yadav, Shailesh 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]] 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) ;
RE: Update Query
Hi, I'm on DIGEST, so I don't know if that's been answered already, or not. But anyhow, what about: update ( select t.field1 f, t.field2 v, d.field1 g, d.field2 w from testtable1 t, testtable2 d where t.id = d.id) set f = g, v = w HTH Dieter Buecherl From: Eric D. Pierce [EMAIL PROTECTED] Date: Wed, 30 May 2001 13:02:14 -0700 Subject: Re: Update Query ha, ha. you got bit by the null update thing too, I remember it well! :) except for maybe some newfangled oracle8 features, the ugly one is the traditional way the manual says to do it (iirc). I have many many scripts with that kind of code in them since we load mainframe datafiles into the local oracle apps, and do a lot of cross-table updates (non-normalized, but that is mostly ok since it is archive data). you *can* pretty it up a bit by using better formatting, e.g., update tablea a set ( a.firstname, a.lastname ) = ( select b.firstname, b.lastname from tableb b where b.id = a.id ) where a.id in ( select b2.id from tableb b2 ) / On 30 May 2001, at 11:56, CC Harvest wrote: Date sent: Wed, 30 May 2001 11:56:14 -0800 To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Seems like Oracle Doesn't have to the query like this: update tablea set firstname=tableb.firstname, lastname=tableb.lastname where tableb.id = tablea.id; I can have one, it works if it has a match for the two tables, otherwise the two columns updated to null: update tablea set (firstname,lastname) =(select firstname,lastname from tableb where tableb.id=tablea.id); Then the following one works, but very ugly: update tablea set (firstname,lastname) =(select firstname,lastname from tableb where tableb.id=tablea.id) where exists( select 'x' from tableb where tableb.id=tablea.id) ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Buecherl Dieter (BUE) 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
ha, ha. you got bit by the null update thing too, I remember it well! :) except for maybe some newfangled oracle8 features, the ugly one is the traditional way the manual says to do it (iirc). I have many many scripts with that kind of code in them since we load mainframe datafiles into the local oracle apps, and do a lot of cross-table updates (non-normalized, but that is mostly ok since it is archive data). you *can* pretty it up a bit by using better formatting, e.g., update tablea a set ( a.firstname, a.lastname ) = ( select b.firstname, b.lastname from tableb b where b.id = a.id ) where a.id in ( select b2.id from tableb b2 ) / On 30 May 2001, at 11:56, CC Harvest wrote: Date sent: Wed, 30 May 2001 11:56:14 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Seems like Oracle Doesn't have to the query like this: update tablea set firstname=tableb.firstname, lastname=tableb.lastname where tableb.id = tablea.id; I can have one, it works if it has a match for the two tables, otherwise the two columns updated to null: update tablea set (firstname,lastname) =(select firstname,lastname from tableb where tableb.id=tablea.id); Then the following one works, but very ugly: update tablea set (firstname,lastname) =(select firstname,lastname from tableb where tableb.id=tablea.id) where exists( select 'x' from tableb where tableb.id=tablea.id) ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce 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
if it was for one column you could use nvl or decode update tablea set firstname=nvl((select firstname from tableb where tablea.id=tablea.id), firstname); Alex Hillman -Original Message- Sent: Wednesday, May 30, 2001 3:56 PM To: Multiple recipients of list ORACLE-L Seems like Oracle Doesn't have to the query like this: update tablea set firstname=tableb.firstname, lastname=tableb.lastname where tableb.id = tablea.id; I can have one, it works if it has a match for the two tables, otherwise the two columns updated to null: update tablea set (firstname,lastname) =(select firstname,lastname from tableb where tableb.id=tablea.id); Then the following one works, but very ugly: update tablea set (firstname,lastname) =(select firstname,lastname from tableb where tableb.id=tablea.id) where exists( select 'x' from tableb where tableb.id=tablea.id) Any suggestions? TIA Chris Harvest __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest 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: Hillman, Alex 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).
