Smart Update, How ?

From: steven wndy <wndyu_at_yahoo.com>
Date: Tue, 12 Mar 2002 13:33:38 -0800
Message-ID: <F001.00426301.20020312133338@fatcity.com>

Hello list:

In my cursor loop, I have an update statement, see below. Now I need to the update only for the field (in that cursor) that has data in it. (which means when is null, that field will not be updated).
- Dynamic SQL can do it. But now for many reasons that I can't use Dynamic SQL in this code. Hard for other people to maintain
- And I believe use multiple "if XXX is not null then update ...", this is accomplishable. But that will be a lot of "if .. then ... update ..." statement.

Is there a smarter way to do this update? I don't know if DECODE can be used here. at least I don't know how. Any ideas? Many Thanks

UPDATE KOMP SET C_INVENTORY_DATE =
DECODE(rec_UpdIES.PHYINVENTORYDATE, NULL, NULL, TO_DATE(rec_UpdIES.PHYINVENTORYDATE, 'YYYYMMDD')),

KFLDC03 = RTRIM(rec_UpdIES.RESPONSIBLEDEPT),    
KFLDC24 = RTRIM(rec_UpdIES.COMPBILLCODE),       
KFLDA01 = DECODE(RTRIM(rec_UpdIES.DATEINSTALLED),

NULL, NULL, TO_DATE(RTRIM(rec_UpdIES.DATEINSTALLED),
'YYYYMMDD')),

KFLDA04 = DECODE(RTRIM(rec_UpdIES.DATERECEIVED), NULL, TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD'), TO_DATE(RTRIM(rec_UpdIES.DATERECEIVED), 'YYYYMMDD')), KFLDC11 = RTRIM(rec_UpdIES.MAINTPONUMBER),  

KFLDC12 = RTRIM(rec_UpdIES.MAINTPOLINE),  

KFLDC13 = RTRIM(rec_UpdIES.MAINTRELEASENO),  

KFLDC14 = RTRIM(rec_UpdIES.MAINTVENDOR),  

GARANTBIS =
TO_DATE(RTRIM(rec_UpdIES.WARRANTYENDDATE),
'YYYYMMDD'),

C_BILLCODE = V_BILLCODE, ACQUISITIONMODE = rec_UpdIES.OWNERSHIPCODE, USERCHG = 'RECEIPT',
DATCHG =
TO_CHAR(SYSDATE,'YYYY-MM-DD-HH24.MI.SS')||'.000000'             WHERE in_IDENT = IDENT;

---

Do You Yahoo!?
Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: steven wndy
  INET: wndyu_at_yahoo.com

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 Tue Mar 12 2002 - 15:33:38 CST