mySQL split values to multiple rows

Question

I have table :

id | f_name    | m_name | l_name
1  | a b c     | null   | null
2  | a b c     | null   | null
3  | a b c     | null   | null

i want output like this :

 id | f_name  | m_name | l_name
1   | a       | b      | c
2   | a       | b      | c
3   | a       | b      | c

My select query works and the data output is like that. I am having challenges updating multiple columns at the same time. Any advise or useful links will be appreciated.

My query:

update tbl_client_test1 set f_name, m_name, l_name = (SELECT
   SUBSTRING_INDEX(SUBSTRING_INDEX(f_name, ' ', 1), ' ', -1) AS f_name,
   If(  length(f_name) - length(replace(f_name, ' ', ''))>1,  
       SUBSTRING_INDEX(SUBSTRING_INDEX(f_name, ' ', 2), ' ', -1) ,NULL) 
           as m_name,
   SUBSTRING_INDEX(SUBSTRING_INDEX(f_name, ' ', 3), ' ', -1) AS l_name
FROM tbl_client_test1) 

Answer 1

For this sample data, this will work:

UPDATE tbl_client_test1 
SET m_name = CASE 
               WHEN CHAR_LENGTH(f_name) - CHAR_LENGTH(REPLACE(f_name, ' ', '')) > 0
                 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(f_name, ' ', 2), ' ', -1)
             END, 
    l_name = CASE 
               WHEN CHAR_LENGTH(f_name) - CHAR_LENGTH(REPLACE(f_name, ' ', '')) > 1
                 THEN SUBSTRING_INDEX(f_name, ' ', -1)
             END,
    f_name = SUBSTRING_INDEX(f_name, ' ', 1);

Note that f_name is updated last in the statement, because it is used in the expressions that update the other 2 columns.

See the demo.