CODEWITHSUNDEEP
mysqlsql-update
user3649739
user3649739

Reputation: 1869

Update with concat when some fields are NULL

I am doing a simple Update on a table whereby I concat three fields:

Update TableA concat(Field1,Field2,Field3)

The issue is that in some records one or more of the fields can be NULL. Those records do not end up with an Update field. In the following situation I'd like the first record's update Field to be BobJones:

+--------+--------+---------+--------------+
| Field1 | Field2 | Field3  | Update       |
+--------+--------+---------+--------------+
| Bob    | NULL   | Jones   | NULL         |
+--------+--------+---------+--------------+
| Mary   | Ann    | Smith   | MaryAnnSmith |
+--------+--------+---------+--------------+
| Bill   |        | Clinton | BillClinton  |
+--------+--------+---------+--------------+

But Update simply ignores it. I tried in that vein Update IGNORE and that failed too. Yeah I know I could do Case When Then thing but it gets more complex than I'd like in this situation and was hoping there was some version of 'Update IGNORE` that would work instead.

Upvotes: 1

Views: 82

Answers (3)

Bernd Buffen
Bernd Buffen

Reputation: 15057

It is shorter to use:

CONCAT_WS('',Field1,Field2,Field3);

Sample

MariaDB []> SELECT CONCAT_WS('', 'Bob',NULL,'Jones',NULL), CONCAT_WS(' ', 'Bob',NULL,'Jones',NULL), CONCAT_WS('Bob',NULL,'Jones',NULL);
+----------------------------------------+-----------------------------------------+------------------------------------+
| CONCAT_WS('', 'Bob',NULL,'Jones',NULL) | CONCAT_WS(' ', 'Bob',NULL,'Jones',NULL) | CONCAT_WS('Bob',NULL,'Jones',NULL) |
+----------------------------------------+-----------------------------------------+------------------------------------+
| BobJones                               | Bob Jones                               | Jones                              |
+----------------------------------------+-----------------------------------------+------------------------------------+
1 row in set (0.00 sec)

MariaDB []>

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

In mysql use ifnull 

concat(ifnull(Field1,''),ifnull(Field2,''),ifnull(Field3,''))

Upvotes: 1

EoinS
EoinS

Reputation: 5482

You can insert zero length string for each NULL value, caught with IFNULL or COALESCE:

Update TableA concat(IFNULL(Field1,''),IFNULL(Field2,''),IFNULL(Field3,''))

Here is a functional example

Upvotes: 0

Related Questions