0

I have the following SQL query in my prepared statement:

UPDATE users SET name = ?, city = ?, country = ?, status = ? WHERE id = ?

Some city and country values in the table are null and some others are not. I want the above query to update name and status; but if city and country are not null, do not update city and country. Is that possible with one query?

One option I have is to have another query before this one and check if the city and country values are null for this user, but I would like to keep it in one query if it's possible.

Dharman
  • 30,962
  • 25
  • 85
  • 135
yenren
  • 450
  • 9
  • 20

1 Answers1

3

You can use COALESCE():

UPDATE users
    SET name = ?,
        city = COALESCE(city, ?),
        country = COALESCE(country, ?),
        status = ?
    WHERE id = ?;

This will keep non-NULL values in city and country. The other columns will be updated.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786