UPDATE ... CONCAT wipes the column or writes 0 instead of the joined text
CONCAT() returns NULL if any argument is NULLShort answer
CONCAT returns NULL when any argument is NULL, so every row with an empty value is wiped by the UPDATE. The plus sign is arithmetic and gives a number, not joined text. Wrap nullable columns in IFNULL, or use CONCAT_WS, and test the expression with SELECT first.
Fix it fast
1Find out which cause you have
Run the expression as a SELECT on the same rows. It shows what the UPDATE would write, before it writes it.
SELECT name, CONCAT(name, '-old') AS result FROM products LIMIT 20;2Match what you see, then jump to its fix
What does the error look like?
CONCAT() returns NULL if any argument is NULL
Cause 1 of 2
A NULL value in one argument makes the whole result NULL
1Check
Count the NULLs in the column you append to.
SELECT COUNT(*) FROM products WHERE name IS NULL;2Fix
Replace NULL with an empty string before joining, or use CONCAT_WS, which skips NULL values after the separator. CONCAT_WS returns NULL only when the separator is NULL.
UPDATE products SET name = CONCAT(IFNULL(name, ''), '-old');
-- or, with a separator between the parts
UPDATE products SET name = CONCAT_WS('-', name, 'old');3Verify
No rows hold NULL because of the update.
SELECT COUNT(*) FROM products WHERE name IS NULL;Confirmed by dev.mysql.com
Cause 2 of 2
The plus sign or || is used instead of CONCAT
1Check
Arithmetic operators apply to numbers, so + does not join strings. And || is a synonym for OR unless the PIPES_AS_CONCAT SQL mode is on.
SELECT @@sql_mode;2Fix
Use CONCAT() in the SET clause.
UPDATE products SET name = CONCAT(name, '-old') WHERE id = 7;3Verify
Read the row back.
SELECT name FROM products WHERE id = 7;Confirmed by dev.mysql.com
How do I stop it coming back?
- Run the SELECT version of every UPDATE and look at ten rows before committing.
- Give text columns NOT NULL DEFAULT '' when empty is a normal state.
- Wrap a bulk UPDATE in a transaction so you can roll it back.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix