CODEWITHSUNDEEP
mysql
Yevgeny Simkin
Yevgeny Simkin

Reputation: 28369

Is it possible to conditionally set only some values in a MySQL update statement?

I have a situation where a user may or may not provide various bits of data that need to go into a row containing that user's info. The issue is that they need to make multiple entries into the row, over time, and I don't want to null out any pre-existing values, if those had been set in the past.

My desire is to do something that looks (in pseudo code) like this:

var a,b,c;// these are the values that the user provides any of which may be null at any time

update user if a != null set name = a,  if b != null set phone = b, if c != null set email c

I've been trying to fashion an IF to work in this manner but I get syntax errors with the various ways I've tried. Hoping someone can shed light on the right way to do this (if it's possible).

TIA

Upvotes: 1

Views: 529

Answers (3)

mvp
mvp

Reputation: 116207

You can use COALESCE like:

UPDATE user SET name = coalesce(a, name), ...

Probably better approach would be to dynamically create your UPDATE statement and include only fields that need to be updated.

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562398

One way to do this is to "default" each column to its original value with COALESCE().

UPDATE user
SET name = COALESCE(a, name),
    phone = COALESCE(b, phone),
    email = COALESCE(c, email)
WHERE ...

COALESCE() returns its first non-null argument, so if a is null, it'll just return the original value of name, so there will be no net change.

MySQL also supports a function ISNULL() which works like COALESCE(), but COALESCE() is standard SQL. Also, COALESCE() supports more than two arguments.

Upvotes: 2

Barmar
Barmar

Reputation: 781210

Here's how to do it purely in SQL:

UPDATE user
SET name = IF(a IS NULL, name, a),
    phone = IF(b IS NULL, phone, b),
    email = IF(c IS NULL, email, c)

Upvotes: 1

Related Questions