RE: Inserstatement

From: Eric D. Pierce <PierceED_at_csus.edu>
Date: Wed, 28 Nov 2001 15:13:48 -0800
Message-ID: <F001.003CFB59.20011128142527@fatcity.com>

ORACLE-L Digest -- Volume 2001, Number 332

>> From: Roland.Skoldblom_at_ica.se
>> 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)" <iain.nicoll_at_calanais.com>

> 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: PierceED_at_csus.edu

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: ListGuru_at_fatcity.com (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).

Received on Wed Nov 28 2001 - 17:13:48 CST