CODEWITHSUNDEEP
mysqlsqlsplitsql-insert
Parsa
Parsa

Reputation: 151

how to insert a fullname column into name and last name columns MYSQL

Have a table named school_teachers which have a full_name column, And another table named teachers which have name and last_name columns.

I'm inserting the school_teachers data into the teachers table,

So how should I insert that full name column into name and last name columns?

Upvotes: 0

Views: 625

Answers (2)

Liki Crus
Liki Crus

Reputation: 2062

Please use the following query.

INSERT INTO teachers (`name`, `last_name`)
SELECT 
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(full_name, ' ', 1),
        ' ',
        - 1
    ) AS `name`,
    IF(
        SUBSTRING_INDEX(full_name, ' ', 1) = full_name,
        "",
        SUBSTRING_INDEX(
            SUBSTRING_INDEX(full_name, ' ', 2),
            ' ',
            - 1
        )
    ) AS last_name
FROM
    `school_teachers` ;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You would concatenate them. I would recommend using concat_ws() because it handles NULL values more elegantly than concat(). So:

insert into school_teachers ( . . ., full_name)
     select . . ., concat_ws(name, last_name)
     from teachers t;

That said, you should probably only be putting the id into a table with essentially two columns, the school_id and the teacher_id. There is no need to copy all the other columns. That is a bad use of databases -- you can use JOIN to get a teacher's name when you need it.

Upvotes: 0

Related Questions