MySQL update/concatenate column if NULL, using data from other columns

Question

I have a contacts table:

id   first_name   last_name   full_name
1    John         Doe         -null-
2    -null-       -null-      Mike Willson
3    Dany         Jean        -null-
4    Peter        -null-      -null-

How can i merge first_name and last_name columns to fill full_name column where full_name is NULL?

I would like to get results as this:

id   first_name   last_name   full_name
1    John         Doe         John Doe 
2    -null-      -null-       Mike Willson
3    Dany         Jean        Dany Jean
4    Peter        -null-      Peter

where does mike come from..?

Barbaros Özhan
– Barbaros Özhan

2019-11-25 20:08:04 +00:00
Commented Nov 25, 2019 at 20:08 — Barbaros Özhan
– Barbaros Özhan, Commented Nov 25, 2019 at 20:08

Paul Spiegel · Accepted Answer · 2019-11-25 20:16:26Z

2

UPDATE your_table
SET full_name = CONCAT_WS(' ', first_name, last_name)
WHERE full_name IS NULL
  AND COALESCE(first_name, last_name) IS NOT NULL

Will update the full_name if it's NULL and either first_name or last_name is not NULL.

Note that CONCAT_WS() will ignore NULL parameters. So if first_name or last_name is NULL, you will only get the other value without the ' ' space.

See demo on db-fiddle.com

edited Nov 25, 2019 at 20:16

answered Nov 25, 2019 at 20:11

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barbaros Özhan · Accepted Answer · 2019-11-25 20:15:49Z

1

One option would be using CONCAT(), COALESCE() and TRIM() functions together :

UPDATE contacts_table
   SET full_name = TRIM(CONCAT( COALESCE(first_name,''), ' ', COALESCE(last_name,'') ))

without a WHERE condition.

Demo

answered Nov 25, 2019 at 20:15

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Comments

Vadim · Accepted Answer · 2019-11-25 20:23:07Z

1

I'm doing it this way - and it wors fine:

UPDATE `conacts_table`
SET `full_name` = CONCAT(IFNULL(first_name, ''), ' ', IFNULL(last_name, ''))
WHERE `full_name` IS NULL

Just wonder if it's the optimal way to do it?

edited Nov 25, 2019 at 20:23

answered Nov 25, 2019 at 19:51

Vadim

5997 silver badges14 bronze badges

1 Comment

Salman Arshad Over a year ago

That and a couple of TRIMs, and a NULLIF if the concatenated result is an empty string.

Collectives™ on Stack Overflow

MySQL update/concatenate column if NULL, using data from other columns

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related