Update with NULL columns in oracle

Question

I have this table

Quantity | Response | Month | Year | Name
__________________________________________
2365      'Response1'  3      2014  (null)
1420      'Response2'  3      2014  'Name1'
2365      'Response1'  3      2014  (null)
 750      (null)       3      2014  'Name2'
  65      (null)       3      2014  (null)

I'm running this update in a stored procedure

update Table1 
set Quantity = q, 
    Response = resp, 
    Month = monthv, 
    Year = yearv, 
    Name = namescreen   
where   Month = monthv 
    and Year = yearv 
    and Response = resp 
    and Name = namescreen;

the columns with null values are not getting updated

Quantity | Response | Month | Year | Name
__________________________________________
2365      'Response1'  3      2014  (null)
1500      'Response2'  3      2014  'Name1'
2365      'Response1'  3      2014  (null)
 750      (null)       3      2014  'Name2'
  65      (null)       3      2014  (null)

How can I solve this?

and Response=resp: if trying to update a column with a NULL value, wrap in an NVL using a dummy value: and NVL(Response, '-1') = NVL(resp, '-1') — Glenn
– Glenn, Commented Apr 25, 2014 at 22:39
@Glenn That can work, but only if there is a specific value that does not exist in the table, and never will. Otherwise, it's just a matter of time until you get a false positive and update the wrong row. — user743382
– user743382, Commented Apr 25, 2014 at 22:41

wvdz · Accepted Answer · 2014-04-25 22:34:28Z

1

It's not clear from your question why you think these rows should be updated, but to check for NULL values you have to use IS NULL. This is because NULL is interpreted as 'Unknown' in standard SQL. This means that most conditional checks that involve null values will also yield 'Unknown'.

Consider this truth table:

c1   | c2    | c1 AND c2  | c1 OR c2
----------------------------------
null | true  | null      | true
null | null  | null      | null
null | false | false     | null

answered Apr 25, 2014 at 22:34

wvdz

16.2k4 gold badges60 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

aex · Accepted Answer · 2014-04-25 22:34:35Z

0

update Table1 set Quantity=q, Response=resp, Month=monthv, Year=yearv, Name=namescreen   
where Month = monthv and Year=yearv and Response=resp and Name=namescreen;

your saying UPDATE TABLES WHICH ARE HAVE ALL THE RAWS use this

update Table1 set Quantity=q, Response=resp, Month=monthv, Year=yearv, Name=namescreen   
where Quantity=q;

you have to check use where in one value not both of them

answered Apr 25, 2014 at 22:34

aex

851 silver badge9 bronze badges

Comments

Chains · Accepted Answer · 2014-04-25 22:50:36Z

Not sure why you're using the same value in your SET statement that you're using in your WHERE clause -- but that's one (potential) problem. (Update month = April where month already = April...can you see it?) Usually you'd either use a key to find the record, or else use two variables -- old value and new value -- for example:

SET Year = new_yearv WHERE Year = old_yearv

or

SET Year = yearv WHERE primary_key_field = 5

The other problem (potentially) is that you might not be passing your stored procedure NULL values like you think. Could be they're converting to empty strings before they're passed or something like that. Anyway, try something like this:

WHERE (Year=yearv OR Year IS NULL) AND (Month = monthv OR Month IS NULL), etc.

Gordon Linoff · Accepted Answer · 2014-04-26 02:14:31Z

0

I think you only want to set the quantity when the columns match the input values. This query should accomplish that:

update Table1 
    set Quantity = q 
where (Month = monthv or Month is null and monthv is null) and
      (Year = yearv or Year is null and yearv is null) and
      (Response = resp or Response is null and resp is null) and
      (Name = namescreen or Name is NULL or namescreen is null);

answered Apr 26, 2014 at 2:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Update with NULL columns in oracle

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related