Update Two Column Field By Splitting The VAlue from One Column in SQL.

Update Two Column Field By Splitting The VAlue from One Column in SQL.

Here the split will be done depending on space in name.
For Single name the lastname field will be entered as NULL.
If The Name (Like: Aathav Sharma) Field has Two words seperated by space,
then the firstname will be the first word (Aathav) and the other word will be the last name (Sharma).

#For updating Particular Rows::
SQL QUERY::

UPDATE user u2
set
u2.first_name = IF(
                        LOCATE(' ', `name`) > 0,
                        SUBSTRING(`name`, 1, LOCATE(' ', `name`) - 1),
                        `name`
                    ),
    u2.last_name  = IF(
                        LOCATE(' ', `name`) > 0,
                        SUBSTRING(`name`, LOCATE(' ', `name`) + 1),
                        NULL
                    )
WHERE
u2.id <26;


#For Updating All Rows::

SQL QUERY::

UPDATE os_user u2
set
u2.first_name = IF(
                        LOCATE(' ', `name`) > 0,
                        SUBSTRING(`name`, 1, LOCATE(' ', `name`) - 1),
                        `name`
                    ),
    u2.last_name  = IF(
                        LOCATE(' ', `name`) > 0,
                        SUBSTRING(`name`, LOCATE(' ', `name`) + 1),
                        NULL
                    )

WHERE 1;