MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl_client_test1 (`id` INTEGER, `f_name` VARCHAR(5), `m_name` VARCHAR(4), `l_name` VARCHAR(4));

INSERT INTO tbl_client_test1 (`id`, `f_name`, `m_name`, `l_name`) VALUES
  ('1', 'a b c', null, null),
  ('2', 'a b c', null, null),
  ('3', 'a b c', null, null);
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);
SELECT * FROM tbl_client_test1;


id
f_name
m_name
l_name


1
a
b
c

2
a
b
c

3
a
b
c