CODEWITHSUNDEEP
sqloracle-databasesql-update
Varun Rao
Varun Rao

Reputation: 801

Oracle Update updating NULL instead of value?

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.

Upvotes: 2

Views: 8612

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

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>

Upvotes: 2

Related Questions