sql - Update attribute only when value is not nil -

i have simple sql statement:

update people  set (updated_at, first_name, last_name, email) =      (now(), r.first_name, r.last_name, r.email)  id = r.p_id; 

i want set email value when r.email not nil.

is there way in 1 query?

i assume part of query

you need add conditional. depending on dbms (postgresql, mysql) need different solutions.

---

postgresql

you'll need coalesce. coalesce function returns first of arguments not null.

update people set (updated_at, first_name, last_name, email) = (now(), r.first_name, r.last_name,      (coalesce (r.email, people.email))  people, r people.id = r.p_id 

---

mysql

you'll need if condition:

if (r.email null, people.email, r.email) 

so query need perform should similar to:

update people set (updated_at, first_name, last_name, email) = (now(), r.first_name, r.last_name,      (if (r.email null, people.email, r.email))  people, r people.id = r.p_id