UPDATE with SELECT subquery: How to avoid nulls 1

[thermidor]

All:

I have an UPDATE statement that uses a SELECT subquery:
UPDATE L_BUDGET bud
SET bud.acct_unit =
(SELECT Xref.NEWCENTER
FROM L_CROSS_REF Xref
WHERE ... );

However, in cases where there is no match, the UPDATE sets the field to null. I need the UPDATE to not modify the data where there is no match.

Probably this has been asked before, but I can 't think of how to search for it. I'd appreciate any help.

TIA,
Sven

 

[LKBrwnDBA]

Try this:

UPDATE L_BUDGET bud            
  SET bud.acct_unit = 
    (SELECT Xref.NEWCENTER
      FROM L_CROSS_REF Xref  
        WHERE ... )
  WHERE EXISTS 
    (SELECT 1
      FROM L_CROSS_REF Xref    
        WHERE ... );

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb