RE: Update query
Roland, How about update B set PRICE = (select NEW_PRICE from A where ) 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).
Update query
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).
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
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).
Update query
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).
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??? 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]] > > 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) ;
update query??? HELP!!!
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).
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
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).
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).
Update Query
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).
