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, Sep 02 '19 at 09: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, Sep 02 '19 at 09: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, Sep 02 '19 at 09: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, Sep 02 '19 at 09: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, Sep 02 '19 at 09:42

score 7 · Accepted Answer · edited Sep 02 '19 at 09:43

7

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 02 '19 at 09:43

Your Common Sense

156,878
40
214
345

answered Sep 02 '19 at 09:36

Andrii Filenko

954
7
17

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) – dataviews Apr 23 '20 at 17:23

Mysql UPDATE query, keep the existing value on NULL

1 Answers1