Oracle SQL update query only updates values if they are zero

Question

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

Is it possible to put it all into one query?

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

and

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

+3

Tom 09 Mar 12 at 11:35

3 answers

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 case - it returns the first non-zero value.

+1

David Faber 09 Mar 12 at 11:42

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

0

Mikael Eriksson 09 Mar 12 at 11:44

Ollie · Accepted Answer · 2012-03-09T11:40:14+0000

You may try:

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

Although, it can fire update triggers even for rows that barely change.

Oracle SQL update query only updates values ​​if they are zero