在mysql查询中将一列值分解为两列？

Question

I want to split one column into two column and update into other two column.

Like below.

Name              FirstName      LastName
ABC NAME             ABC           NAME
PQR RNP              PQR           RNP
SUM NUM              SUM           NUM

explode and update name field value and update into same tables two column FirstName and LastName.

Answer 1

If you want to do this directly in MySQL, you would need the following queries:

ALTER TABLE yourtable ADD COLUMN FirstName VARCHAR(30),
                      ADD COLUMN LastName VARCHAR(30);
UPDATE yourtable 
    SET FirstName = SUBSTRING_INDEX(Name, ' ', 1),
        LastName = IF(LOCATE(' ', Name) = 0, '', SUBSTRING_INDEX(Name, ' ', -1));
ALTER TABLE yourtable DROP COLUMN Name;

Always back up your table before making structural modifications.