CODEWITHSUNDEEP
mysqlsql
Vadim
Vadim

Reputation: 569

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

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

Upvotes: 0

Views: 148

Answers (3)

Vadim
Vadim

Reputation: 569

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?

Upvotes: 1

Paul Spiegel
Paul Spiegel

Reputation: 31772

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

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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

Upvotes: 1

Related Questions