SQL UPDATE, but only if the old value is null

Question

I have been using a sql like this to update a list of properties in my database:

update my_table set a = ?, b = ?, c = ?, d = ?,  where customer = ?

But I want to update a property with a new value ONLY if it does not have a value in the database already. How can I do that?

Answer 1

In MS SQL something like this (assuming non value means database NULL) should work:

update 
  my_table 
set 
  a = COALESCE(a, ?), 
  b = COALESCE(b, ?), 
  c = COALESCE(c, ?), 
  d = COALESCE(d, ?)
where 
  customer = ?

COALESCE() returns first non null value from its arguments.

Answer 2

In MySQL, you could do:

UPDATE my_table 
SET
a = IFNULL(a, ?),
b = IFNULL(b, ?),
c = IFNULL(c, ?),
d = IFNULL(d, ?)
where customer = ?

Answer 3

If you're talking about doing that on a field per field basis in the row:

update my_table
set a = coalesce(a, ?),
    b = coalesce(b, ?),
    c = coalesce(c, ?)
where customer = ?

Answer 4

If you are using oracle:

update my_table 
   set a = nvl(a, new_a_value),
       b = nvl(b, new_b_value),
       c = nvl(c, new_c_value),
       d = nvl(d, new_d_value),
 where customer = ?

If you are not using Oracle, please update question with RDBMS you are using, or look for a nvl like function in your database.

Answer 5

Use combinations of "is null" or "is not null" in your queries, i.e.

update my_table set a = ? where customer = ? and a is null

Of course, this only works for columns where null is a legal value. It's actually hard to know exactly what query will work for you without knowing the constraints on the various columns.

Answer 6

update YourTable
    set a = coalesce(a, NewValueA),
        b = coalesce(b, NewValueB),
        ...
    where customer = ?

Answer 7

Try this code SQL native it's work for me very well :

UPDATE table 
SET field = 'NO'
WHERE field 
IS NULL
OR field = ''

Update just NULL value or empty.