update field if :new.field is not null

[anniez]

I have a trigger that, if it can't find a match, inserts a new record in TableA with all the data from the new record in tableB.
If it finds a match it should update field a,b,c,d,e... with :new.a, :new.b, :new.c, :new.d, :new.e...
but only if the new a,b,c,d,e... are not null.


SET
WARD=(select wardID from dmhis.V_WARD_CLIN where to_charnew.adm_unit) = dmhis.V_WARD_CLIN.clinic),

CLINIC_ID = DECODE(SUBSTRNEW.ADM_TYPE,1,1),2,to_charNEW.adm_unit),1,' '),

PT_STAT = :NEW.ADM_TYPE,
ADMDATE= :NEW.ADM_DATE,
ONLIST =DECODE(SUBSTRNEW.ADM_TYPE,1,1),1,' ',2,'CLI'),
DOB=:NEW.BIRTH_DT,
SEX = :NEW.SEX,
PLNAME = UPPERNEW.LAST_NAME),
PFNAME = UPPERNEW.FIRST_NAME),
MOD_DATE = :NEW.CREATE_DATE,
DSCDONE = decode(substrnew.ADM_UNIT,1,1),3,' '),
WBU_PWORK = decode(substrnew.ADM_UNIT,1,1),3,'No'),
dscdate = decodenew.ADM_TYPE,1,NULL),
ssn=:new.ssn,
.....
it all works fine but I only want certain fields in TableA updated if the corresponding fields in TAbleB are not null.
Ex DOB, SocSecNum - if correct in tablea, a null in tableb would replace the correct info with a blank.
Thanks

 

[sfvb]

I'm not positive I understand what your asking. What I think you are saying is this:

The part of the code that inserts a new record into tableA works fine. The problem is the part of the code that updates existing records in TableA is using all the values from TableB, when it should only update the parts of TableA where the values on TableB are filled in.

For example, in TableA you have:

...
DOB    = null
SEX    = 'F'
PLNAME = 'JEAN'
PFNAME = 'SPRAGUE'
ssn    = null
...

IN the :NEW data block you have:

...
DOB    = null
SEX    = null
PLNAME = null
PFNAME = 'VAN BUREN'
ssn    = '123456789'
...

and you want tableA to end up being:

...
DOB    = null
SEX    = 'F'
PLNAME = 'JEAN'
PFNAME = 'VAN BUREN'
ssn    = '123456789'
...

If this is what you want then this should work:
UPDATE tableA
SET

...
DOB    = nvl(:NEW.BIRTH_DT, DOB),
PLNAME = nvl(UPPER(:NEW.LAST_NAME), PLNAME),
PFNAME = nvl(UPPER(:NEW.FIRST_NAME, PFNAME),
ssn    = nvl(:new.ssn, ssn),
...
WHERE  whatever = someOtherWhatever

If my solution isn't what your trying to solve, then please go into more detail.

Steve