Oracle SQL update query only update values if they are null

Question

I have the following queries where I am updating values only if they are null.

Is it possible to put all of these into a single query?

UPDATE test
SET test1 = 'hello'
WHERE test1 IS NULL

and

UPDATE test
SET test2 = 'world'
WHERE test2 IS NULL

score 10 · Accepted Answer · answered Mar 09 '12 at 11:40

10

You could try:

UPDATE test
   SET test1 = NVL(test1, 'hello'),
       test2 = NVL(test2, 'world')
 WHERE test2 IS NULL
    OR test1 IS NULL;

Though it may fire your update triggers even for the rows that are effectively unchanged.

answered Mar 09 '12 at 11:40

Ollie

The trigger issue will be relevent whether you use COALESCE or NVL. IMHO COALESCE is overkill for a straightforward null test. – Ollie Mar 09 '12 at 11:50
The only reason I would choose COALESCE over NVL is that it is standard while the latter is not. Either way is fine with me. :) – David Faber Mar 09 '12 at 11:59
Could you define "Standard"? I believe NVL has been used by Oracle for quite some time and this is an Oracle question. – Ollie Mar 09 '12 at 12:01
1

Sorry, ANSI standard. Also NVL evaluates both arguments while COALESCE returns the first non-null, only evaluating up to that point. – David Faber Mar 09 '12 at 12:05
I like the point about NVL evaluating both arguments and COALESCE only to the first non-null. For this question it would be barel noticeable but in the case of a subquery in the NVL or similar then it could be much more efficient. – Ollie Mar 09 '12 at 12:55
Here's another difference: http://stackoverflow.com/questions/950084/oracle-differences-between-nvl-and-coalesce – David Faber Mar 09 '12 at 13:00
I appreciate those differences and under certain circumstances it would really be worth using COALESCE over NVL, however, in terms of readability and obvious code intent it could be argued that NVL is more descriptive of the purpose of the code. Horses for courses. – Ollie Mar 09 '12 at 13:19
Agreed. And NVL will save keystrokes in this case. – David Faber Mar 09 '12 at 13:30

score 2 · Answer 2 · answered Mar 09 '12 at 11:42

2

UPDATE test
   SET test1 = COALESCE(test1, 'hello')
     , test2 = COALESCE(test2, 'hello')
 WHERE test1 IS NULL OR test2 IS NULL

COALESCE() works similarly to NVL() in this circumstance -- returning the first non-null value.

answered Mar 09 '12 at 11:42

David Faber

So will this only update test1 if test1 is null and test2 = "abc"?? – Tom Mar 09 '12 at 11:47
1

@Tom, it will only update test1 if it is null regardless of what is in (or not in) test2. – Ollie Mar 09 '12 at 11:48
Do I still need the where in this case?... I will of course add a `WHERE user_id = 'myuserid'` – Tom Mar 09 '12 at 11:53
Technically speaking it will update test1 if it's non-null but it will set it to its current value. That's why triggers can be an issue. They will fire even though you're updating a column to its current value. – David Faber Mar 09 '12 at 11:56
if you don't include the `WHERE` clause to test for nulls, rows where both test1 and test2 are not null will needlessly be updated. – Ollie Mar 09 '12 at 11:59
Right, I was thinking of the case where one has an UPDATE OF test1 trigger; it will fire even when only test2 is null. – David Faber Mar 09 '12 at 12:02
Tried `COALESCE` and it gave me `ORA-12704: character set mismatch`, but had no problems at all when using `NVL` in the same query. – vapcguy Aug 08 '16 at 20:23

score 0 · Answer 3 · answered Mar 09 '12 at 11:44

0

UPDATE test
SET Test1 = COALESCE(test1, 'hello'),
    Test2 = COALESCE(test2, 'world')
WHERE test1 IS NULL OR
      test2 IS NULL

answered Mar 09 '12 at 11:44

Mikael Eriksson

3 Answers3