Combining INSERT SELECT and VARIABLES

Question

I have two table users_new and users_old with some count of rows. I would like to insert data from users_old to users_new by using insert select.

Table users_old has one field name, which contains first name and last name.
Table users_new must have two fields, first name (name)and last name (surname).

I don't know how combine them. Any advice?

SET @str="";
SET @firstName="";
SET @lastName="";

INSERT INTO users_new  (name, surname)
VALUES(
    SELECT @firstName,@lastName;
    @str=select name
        FROM  users_old  
SET @firstName = SUBSTRING_INDEX(@str, ' ', 1);
SET @lastName=LTRIM(REPLACE(@str, @firstName, ''))
);

Giorgos Betsos · Accepted Answer · 2016-03-16 14:39:46Z

1

If the splitting criterion is the space, as implied by the sql code posted, then you can use a simple INSERT INTO SELECT statement:

INSERT INTO users_new (name, surname)
SELECT SUBSTRING_INDEX(name, ' ', 1), SUBSTRING_INDEX(name, ' ', -1)
FROM users_old

The last name is selected by a second call to SUBSTRING_INDEX: this one gets the second part of the name field, because of the-1 argument.

answered Mar 16, 2016 at 14:39

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bence Gajdán Over a year ago

It's only going to work, if all the names are consists of a last and a first name. If you only got one word for a name, it will be duplicated in both cols, and if you have a middle name in the mix, the last name will be lost.

Collectives™ on Stack Overflow

Combining INSERT SELECT and VARIABLES

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related