MySql Update Select From

I have had to solve this same issue a number of times. It seems that every time I have to do it I cannot remember exactly how to do it. I then end up searching the Internet to find the solution so I am going to post an example here so that I do not have to search for it and hopefully it will help someone else.

The problem I have encountered is Updating a table in MySql with data Selected from the same table. It appears that MySQL doesn’t allow referring to a table that’s targeted for update in a FROM clause.

The latest issue was normalizing a table in a database I inherited. This had started out as a small database that served its purpose but as the organization grew it created more problems. In this example each person's entire name was stored in a single column.

+--------------------+
| name               |
+--------------------+
| John Doe           |
| Billy Joe Bob      |
| James \"Jim\" Dean |
+--------------------+

I wanted to split this into first, middle, last and nick names for different purposes. So first let us define a table.

create table people ( id int primary key, name varchar(45) );

insert into people values( 1, 'John Doe'),
                         ( 2, 'Billy Joe Bob'),
                         ( 3, 'James \"Jim\" Dean');

For now I want to split the first and last names into separate columns.
First we need to define those columns so we will add them to the existing table;

alter table people add column fname varchar(20) after id;

alter table people add column lname varchar(20) after fname;

Now that the columns are added we can split the names and insert them into the same table.

UPDATE people p SET fname=( 
       SELECT SUBSTRING_INDEX(name,' ',1) 
       FROM ( SELECT * FROM people ) AS X where p.id=X.id );

The Substring Index splits the first name. Then select from the table as X works because MySql materializes subqueries in the FROM clause as temporary tables. This allows us to bypass the problem.