RE: Inserstatement
ORACLE-L Digest -- Volume 2001, Number 332 -- From: [EMAIL PROTECTED] Date: Tue, 27 Nov 2001 12:45:39 +0100 Subject: Inserstatement How can I update one field in table X from another table, table P. Table P have 5 different fields but only one of them should be used to update table X. Give me an example on a sql statement for this. -- From: Nicoll, Iain (Calanais) [EMAIL PROTECTED] Date: Tue, 27 Nov 2001 13:33:32 - Subject: RE: Inserstatement ... update x set field = (select field1 from p where p.join_field = x.join_field) where conditions step 1: select 'rtfm' from dual; /* joke */ step2: note that the where conditions can be quite important when updating from a subquery, as given above. iirc, one can inadvertently null out the values of non-matching rows in the target table unless something like the following is specified: | update x | set field = (select field1 | from p | where p.join_field = x.join_field) | where | x.join_field in | (select p.join_field | from p | ) looks ugly and stupid, but it is apparently a structural glitch in SQL (not present in other relational languages like the SQL alternative in Ingres?). anyh, fwiw, here in blue suede pseudocode is how i debug/test this kind of stuff: 1.select original data into output file for comparitive purposes eg, select count(*), x.field from x group by x.field 2. run the update 3. repeat #1 to see if the changes are what you want 4. (for debug/testing only, in case of wtf:) rollback if it works, in a subsequent run, comment out #1, #3 and #4 and add: 5. commit; then rerun for final (committed) results. brgrds, ep (data janitor to the gods) -- 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: Inserstatement [lock/update subquery]
[EMAIL PROTECTED] folks, also, don't forget something along these lines: squeal whenever sqlerror exit sql.code ; squeal LOCK TABLE x in exclusive mode nowait ; /* piss off users */ squeal whenever sqlerror continue ; ORACLE-L Digest -- Volume 2001, Number 332 -- From: [EMAIL PROTECTED] Date: Tue, 27 Nov 2001 12:45:39 +0100 Subject: Inserstatement ... | update |x |set |field = | ( |select | field1 | from | p | where | p.join_field = x.join_field | ) | where | x.join_field in | ( | select | p.join_field | from | p | ) ... -- 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: Inserstatement
You cannot achieve this by one sql statement. Instead you should consider use PL/SQL. Make your own procedure or an anonymous PL/SQL block. I would like to give you an example but you have to tell more about your problem, like the update should be done based on a relation between those 2 tables... and furthermore it's an insert or an update what you were talking about? If you want just an insert you can use something like: INSERT INTO X (field_in_X) SELECT field_in_P FROM P Regards Iulian -Original Message- Sent: Tuesday, November 27, 2001 1:45 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** How can I update one field in table X from another table, table P. Table P have 5 different fields but only one of them should be used to update table X. Give me an example on a sql statement for this. Sincerely Roland S -- 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). ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- 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: Inserstatement
I hate to disagree but why couldn't you update x set field = (select field1 from p where p.join_field = x.join_field) where conditions Iain Nicoll -Original Message- Sent: Tuesday, November 27, 2001 12:45 PM To: Multiple recipients of list ORACLE-L You cannot achieve this by one sql statement. Instead you should consider use PL/SQL. Make your own procedure or an anonymous PL/SQL block. I would like to give you an example but you have to tell more about your problem, like the update should be done based on a relation between those 2 tables... and furthermore it's an insert or an update what you were talking about? If you want just an insert you can use something like: INSERT INTO X (field_in_X) SELECT field_in_P FROM P Regards Iulian -Original Message- Sent: Tuesday, November 27, 2001 1:45 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** How can I update one field in table X from another table, table P. Table P have 5 different fields but only one of them should be used to update table X. Give me an example on a sql statement for this. Sincerely Roland S -- 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). ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- 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: Nicoll, Iain (Calanais) 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: Inserstatement
Yeah, Iain, you are right, and I don't hate to recognize. Iulian -Original Message- Sent: Tuesday, November 27, 2001 3:35 PM To: Multiple recipients of list ORACLE-L I hate to disagree but why couldn't you update x set field = (select field1 from p where p.join_field = x.join_field) where conditions Iain Nicoll -Original Message- Sent: Tuesday, November 27, 2001 12:45 PM To: Multiple recipients of list ORACLE-L You cannot achieve this by one sql statement. Instead you should consider use PL/SQL. Make your own procedure or an anonymous PL/SQL block. I would like to give you an example but you have to tell more about your problem, like the update should be done based on a relation between those 2 tables... and furthermore it's an insert or an update what you were talking about? If you want just an insert you can use something like: INSERT INTO X (field_in_X) SELECT field_in_P FROM P Regards Iulian -Original Message- Sent: Tuesday, November 27, 2001 1:45 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** How can I update one field in table X from another table, table P. Table P have 5 different fields but only one of them should be used to update table X. Give me an example on a sql statement for this. Sincerely Roland S -- 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). ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Mobil Rom is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- 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: Nicoll, Iain (Calanais) 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: 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).
