Combining multiple text fields into one in MySQL

Question

I have a list of users in a table, with separate fields for first, middle, and last name. For various reasons, I need to change 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?

Answer 1

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;