CODEWITHSUNDEEP
mysqlsql
MrGlass
MrGlass

Reputation: 9262

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 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?

Upvotes: 5

Views: 11498

Answers (4)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

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;

Upvotes: 7

mozillanerd
mozillanerd

Reputation: 560

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

Upvotes: 0

Erik Mitchell
Erik Mitchell

Reputation: 425

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

Upvotes: 0

Neil N
Neil N

Reputation: 25258

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

Upvotes: 1

Related Questions