Mysql UPDATE query, keep the existing value on NULL

Question

I have the following code so far:

  $update = $connection->prepare("UPDATE recommendations_tbl
                                     SET IFNULL(?, PLEASE_DO_NOTHING()),
                                         IFNULL(?, PLEASE_DO_NOTHING()),
                                     WHERE recommendation_userid = ?
                                     ");


  $update->bindValue(1, $recommendationsDataInput["recommendationCategoryNameInput"]);
  $update->bindValue(2, $recommendationsDataInput["recommendationManufacturerNameInput"]);
  $update->bindValue(3, $recommendationUserID);


  $updateResult = $insertion->execute();

Now, I'm pretty new to prepared statements and I just learned about using IFNULL inside prepared statements here: Logic to NOT insert a value to column X in a prepared statement

I'm not sure if the syntax, apart from my little placeholder PLEASE_DO_NOTHING(), would actually work at all. However, my main issue right now is that I don't know if I can actually use IFNULL() in this way: If the inputvalue is defined, SET the new value. If the inputvalue is undefined (=NULL), don't do anything.

Is this possible and if so, how can I do it?

You need to build different statements in your application layer. If you do not want to change the value of some field just don't include it in SQL. — Dharman
– Dharman ♦, Commented Sep 2, 2019 at 9:34
@Dharman Yes I could do this, and I also know how to do this, but in this case, I want to learn how to do it the way I described above. — Narktor
– Narktor, Commented Sep 2, 2019 at 9:36
@baryon123 is it worth learning though? think in my 5/6 years of PHP, I've never seen this or thought of a logical use for this xD — treyBake
– treyBake, Commented Sep 2, 2019 at 9:37
to be honest, this question is not much related to prepared statements (but rather to general SQL), but otherwise it's a good one — Your Common Sense
– Your Common Sense, Commented Sep 2, 2019 at 9:41
@treyBake well maybe ^^ but you never know when you might have use for this, and in my case, I like to allow my user to just leave inputfields empty and then process the data accordingly without handling it with much more extensive logic on php side. Maybe I wont ever need it anymore, but I'm currently just testing around and as I said, who knows what use it might have in the future :D — Narktor
– Narktor, Commented Sep 2, 2019 at 9:42

Your Common Sense · Accepted Answer · 2019-09-02 09:43:25Z

8

Actually, you can do it either with PHP or with IFNULL.

With PHP you can do it using a different prepared statement according to your NULL object.

With IFNULL you can try setting the same value as in the current column, eg your columns are called recommendation_category_name and recommendation_manufacturer_name:

$connection->prepare("UPDATE recommendations_tbl SET
          recommendation_category_name = IFNULL(?, recommendation_category_name),
          recommendation_manufacturer_name = IFNULL(?, recommendation_manufacturer_name),
          WHERE recommendation_userid = ?
");

edited Sep 2, 2019 at 9:43

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

answered Sep 2, 2019 at 9:36

Andrii Filenko

9847 silver badges18 bronze badges

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

1 Comment

dataviews Over a year ago

I was able to achieve something similar. I'm creating an API, and I wanted to update the fields only if the value is not null. UPDATE users SET first_name = 'Billy', last_name = 'Bob', password = IFNULL(NULL, password)

Collectives™ on Stack Overflow

Mysql UPDATE query, keep the existing value on NULL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related