CODEWITHSUNDEEP
mysqlvariablesselectinsert
Dana
Dana

Reputation: 123

Combining INSERT SELECT and VARIABLES

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, ''))
);

Upvotes: 1

Views: 177

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

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.

Upvotes: 1

Related Questions