Combining multiple text fields into one in MySQL

I have a list of users in a table, with separate fields for first, middle, and last name. For various reasons, I need to chang开发者_Python百科e the database structure such that there is only one "name" field. What is the best/easiest way to migrate my data from the 3 old fields into my one new field?

First add a column that is longer than all 3 combined.

alter table tbl add fullname varchar(100);

Next, update it with the concatenation of the old columns.

update tbl set fullname = concat(lastname, ', ', firstname, ' ', middlename)

(This ends up in the form 'Kirk, John M')

Then, remove the old columns

alter table tbl drop column firstname;
alter table tbl drop column middlename;
alter table tbl drop column lastname;

UPDATE Users SET FullName = FirstName + ' ' + MiddleName + ' ' + LastName

UPDATE Users SET Fullname = CONCAT(Firstname, " ", MiddleName, " ", LastName);

Concatenate using 'expression + expression' The expressions should be non-NULL.