Oracle Update updating NULL instead of value?

Question

I have a update query something like

update employees
set salary = salary - (select sum(salary) from employees where manager_id in (101,102))
where employee_id = 105;

The sql above works fine but the below sql is updating NULL instead of value.

UPDATE table1 a
SET    a.col1 = a.col1 - (SELECT SUM(b.col2)
                          FROM   table2 b
                          WHERE  b.col3 = 'AA'
                                 AND b.col4 = '1234'
                                 AND b.col5 = '123456789'
                                 AND b.col6 = 'O'
                                 AND b.col7 IN ( 1, 2, 3, 4 ))
WHERE  a.col3 = 'AA'
       AND a.col4 = '2313'
       AND a.col5 = '987654321';

Do someone know the reason?

Will it update NULL if salary values have some NULL values in them. (I know it wont because the inner query returns a number value).

It works fine if I hardcode the values for the inner query but fails if i use bind parameters. (However a numeric value is returned both when hardcoded or when bind parameter used. )

I just cant seem to know whats wrong with this simple query.

By the by here is the original sql UPDATE TABLE1 a SET a.COL1 = a.COL1 - (SELECT SUM(b.COL2) FROM TABLE2 b WHERE b.COL3 = 'AA' AND b.COL4 = '1234' AND b.COL5 = '123456789' AND b.COL6 = 'O' AND b.COL7 IN (1,2,3,4) ) WHERE a.COL3 = 'AA' AND a.COL4 = '2313' AND a.COL5 = '987654321' ; — Varun Rao
– Varun Rao, Commented Apr 2, 2015 at 4:43
Better add these details in the question itself rather than commenting. I have edited your question and added it for you this time. — Lalit Kumar B
– Lalit Kumar B, Commented Apr 2, 2015 at 4:54
Regarding your question, you could use NVL, such that it updates only when NOT NULL else no change to the column. See my answer. — Lalit Kumar B
– Lalit Kumar B, Commented Apr 2, 2015 at 5:01

Lalit Kumar B · Accepted Answer · 2015-04-02 05:13:33Z

salary - (select sum(salary)...

If salary or the sub-query returns NULL, then the entire expression results in NULL. Therefore it would update and set the column value to NULL.

To avoid this, use NVL on the entire expression.

For example,

SQL> CREATE TABLE t AS SELECT 1 A FROM dual;

Table created.

SQL>
SQL> SELECT * FROM t;

         A
----------
         1

SQL>
SQL> UPDATE t SET A = A - (SELECT NULL FROM dual);

1 row updated.

SQL>
SQL> SELECT * FROM t;

         A
----------


SQL>

So, it updated with NULL value since the expression resulted in NULL. Let's use NVL to avoid the update when NULL:

SQL> ROLLBACK;

Rollback complete.

SQL> UPDATE t SET A = NVL(A - (SELECT NULL FROM dual), A);

1 row updated.

SQL>
SQL> SELECT * FROM t;

         A
----------
         1

SQL>

Problem solved!

To make it more verbose

The above NVL method is just like IF NULL THEN REPLACE_VALUE.

So, you could write a case expression to look it more verbose, it is just expanding the NVL expression:

SQL> SELECT * FROM t;

         A
----------
         1

SQL> UPDATE t
  2  SET A =
  3    CASE
  4      WHEN (A -
  5        (SELECT NULL FROM dual
  6        )) IS NULL
  7      THEN A
  8      ELSE (A -
  9        (SELECT NULL FROM dual
 10        ))
 11    END;

1 row updated.

SQL> SELECT * FROM t;

         A
----------
         1

SQL>

Collectives™ on Stack Overflow

Oracle Update updating NULL instead of value?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related