Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

UPDATE ... CONCAT wipes the column or writes 0 instead of the joined text

ErrorCONCAT() returns NULL if any argument is NULL

Short 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.

  • Applies to MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1Rows that had a value are right; rows that had NULL are still NULL after the UPDATEA NULL value in one argument makes the whole result NULLFix
    2. 2The column now holds a number such as 0 instead of the joined textThe plus sign or || is used instead of CONCATFix

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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.