This just caused me several moments of pain. Here’s hoping I can remove that for you.
I was needing to update a VARCHAR column by incrementally adding values read from another source. Simplest SQL (I thought) was:
UPDATE table SET field = CONCAT(field, 'value ') WHERE id = ID;
All ran fine. But pretty much all of the fields came back NULL. And to find out you have to actually read the details in the mysql manual. A snip from the official write-up on concat:
CONCAT() returns NULL if any argument is NULL.
There’s the problem. Most/all my starting values were NULL. So concatenating actual values in the field did nothing as the NULL won.
The solution was very simple. Replace the CONCAT() function with CONCAT_WS() where the “ws” bit means “with separator”. Rather than just adding a space after the value in my example, specify the delimiter as the first parameter. This makes the revised SQL command:
UPDATE table SET field = CONCAT_WS(' ', field, 'value ') WHERE id = ID;
It’s a lot more forgiven with NULLs; again snipped from the manual:
- If the separator is NULL, the result is NULL
- CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.
Now it does not matter if there are any NULL values involved. All just works.