Function that is opposite of nvl

Question

I've been scouring the internet trying to find an answer to this but I am coming up empty handed. I was wondering if there is a function that acts as an opposite to the nvl function. Something like this:

UPDATE transaction_review
   SET "function use if null"(review1,review2) = 'Yellow',
       "function use if null"(reason1,reason2) = 'Audit'
 WHERE ACCOUNT = '11111111'

Essentially, the update would pick review2 if review1 is already populated. Is there any function like this? Thanks.

possible duplicate of [Oracle SQL update query only update values if they are null](http://stackoverflow.com/questions/9633398/oracle-sql-update-query-only-update-values-if-they-are-null) — eis, Sep 13 '13 at 16:37
there's a proper answer in the question I linked - use that. — eis, Sep 13 '13 at 16:38
The NVL2 comes close to what you're looking for. You'd need to use dynamic SQL, however, to accomplish your goal as Oracle doesn't allow column names to be chosen dynamically. Share and enjoy. — Bob Jarvis - Слава Україні, Sep 13 '13 at 17:02

score 3 · Answer 1 · answered Feb 07 '19 at 05:08

3

Try NVL2 command in plSQL.

The NVL2 function accepts three parameters. If the first parameter value is not null it returns the value in the second parameter. If the first parameter value is null, it returns the third parameter.

answered Feb 07 '19 at 05:08

Cjo

1,265
13
19

score 1 · Accepted Answer · answered Sep 13 '13 at 16:35

1

If I understand you correctly try do use coalesce function as below

UPDATE transaction_review
set review1 = coalesce(review1,'Yellow'),
    review2 = coalesce(review2,'Audit')
WHERE ACCOUNT = '11111111'

answered Sep 13 '13 at 16:35

Robert

25,425
8
67
81

Yeah I thought `coalesce` just picked the first field that isnt null. – user2405778 Sep 13 '13 at 16:37
@user2405778 That is true :) and I think it solve your problem – Robert Sep 13 '13 at 16:39
No I needed the first field that IS null, not ISNT null. – user2405778 Sep 13 '13 at 16:41
1

@user2405778 if `review1` is null then the update set `Yellow`, if not nothing will happen – Robert Sep 13 '13 at 16:43

score 0 · Answer 3 · answered Sep 13 '13 at 16:41

0

Put the logic in a case statement:

UPDATE transaction_review
set review1 = coalesce(review1,'Yellow'),
    review2 = (case when review1 is null then review2 else 'Audit' end)
WHERE ACCOUNT = '11111111'

answered Sep 13 '13 at 16:41

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 4 · answered Sep 13 '13 at 16:52

It's not possible to conditionally choose the column you wish to update without using dynamic SQL

Maybe try using the NVL2 function as documented here: http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions120.htm

It provides a nice IF..THEN..ELSE syntax which may be appropriate for you requirements.

You would have to have two separate SET statements if you use the approach described above.

Function that is opposite of nvl

4 Answers4