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 -0000
> 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?).
anyhoooo, 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).
